SQL Server Backup Database Error : MS, SQL Server 2K, 8.00.2039, Dell 1600 Series RAID running a WMS application, 3041 Backup Failed to Complete the command BACKUP DATABASE[ASCTrac] TO DISK=N'C:\SQL DB BAK\asctrac031808.bak' WITH NOINIT, NOUNLOAD, NAME=N'ASCTrac backup', NOSKIP, STATS=10, NOFORMAT

May 9, 2008 06:27am pdt

1. chapmandew 1,067,187
2. angelIII 782,194
3. aneeshat... 370,866
4. ee_rlee 331,852
5. dtodd 256,729
6. mark_wills 226,935
7. ScottPle... 183,291
8. acperkins 173,198
9. ptjcb 115,237
10. EugeneZ 103,859
11. brejk 103,840
12. asvforce 103,642
13. bhess1 97,730
14. dportas 80,081
15. BrandonG... 78,437

1. angelIII 9,737,307
2. aneeshat... 4,428,415
3. acperkins 4,161,658
4. ScottPle... 3,997,343
5. arbert 2,245,195
6. Lowfatsp... 2,162,122
7. rafrancisco 1,460,132
8. imran_fast 1,450,838
9. EugeneZ 1,380,021
10. Hilaire 1,221,775
11. Nightman 1,134,404
12. Mr_Peerapol 1,087,815
13. chapmandew 1,067,187
14. ptjcb 1,048,696
15. bhess1 1,034,722

03.27.2008 at 12:35PM PDT, ID: 23275344 | Points: 50

	[x] Attachment Details

SQL Server Backup Database Error

Zone: MS SQL Server

Tags: MS, SQL Server 2K, 8.00.2039, Dell 1600 Series RAID running a WMS application, 3041 Backup Failed to Complete the command BACKUP DATABASE[ASCTrac] TO DISK=N'C:\SQL DB BAK\asctrac031808.bak' WITH NOINIT, NOUNLOAD, NAME=N'ASCTrac backup', NOSKIP, STATS=10, NOFORMAT

Our WMS application vendor recently did a backup of the database on 3/18/08 and named the file asctrac031808.bak. When I go into the SQL Ent Mgr and change the name of this file to a different date, and run the backup, I get this error message. Also the new file name is not kept, and reverts back to the 31808.bak. I need some advise on how to set-up daily backups using the Ent.Mgr./All Tasks/Backup Database. I would like to see the DB backed up nightly and the file named with the system date when backup was run. I have full administrative rights to the domain, and the SQL Server.

Attachments:

SQL-SERVER-DB-BACKUP-Error-Messa.bmp (3.0 MB) (File Type Details)

3041 Error Message

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: cashsistla

Question Asked On: 03.27.2008

Participating Experts: 2

Points: 50

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

03.28.2008 at 03:23AM PDT, ID: 21229102

Rank: Guru

VMontalvao:

Can you click on "Show step details" to see what's really the error message?

03.28.2008 at 04:29AM PDT, ID: 21229373

sqlconsumer:

Have you setup a daily maintenance plan before?

You can use a maintenance to schedule backups to run every evening and the backups will be given the system date and time as part of the file name.

Let me know if you need more info on maintenance plans?

03.28.2008 at 11:58AM PDT, ID: 21233184

cashsistla:

No, I was trying to do backups from ENT MGR - ALL TASKS - BACKUP DATABASE. I have now setup a Maintenance Plan to run nightly backups. This will start tonight around 11 PM. Is there any value to saving the .BAK file by a date/time stamp each night or just have the latest backup from the previous night?

What is the difference between setting up a Maintenance Plan vs the way I was doing from ALL TASKS - BACKUP Database? Both accept a maintenance schedule. I am new to the SQL Server, and do not understand all the intricacies of the best method to do a complete DB backup together with the TL file. I have also attached a copy of the SQL Error Message. Thanks for your assistance.

SQL-SERVER-DB-BACKUP-Error-Messa.bmp (3.0 MB) (File Type Details)

SQL SERVER Error Message 3041

03.31.2008 at 06:22AM PDT, ID: 21245034

Rank: Guru

VMontalvao:

Verify if you are backuping always to same file.
What's SQL Server version (use SELECT @@VERSION in Query Analyzer)?
And how big is the database?

03.31.2008 at 07:54AM PDT, ID: 21245892

sqlconsumer:

Have you checked the SQl server Logs?

You should be able to find more information in there regarding your backup error?

Decideding which method of backup you what to implement really relies on the use of the database in question

03.31.2008 at 09:57AM PDT, ID: 21247115

cashsistla:

MS SQL SERVER 2K 8.00.2039 (X86) is the version. The DB is about 5 GB. From the maintenance schedule that I set up last Friday night, the backups ran fine over the weekend. But this morning when I ran the DBCC SQLPERF(LOGSPASCE), the size fo the Transaction Log File was 30342 MB and 99% of the space used. On Friday these stats were 1043MB and 19% used space. What would have caused the TL file to increase 30 fold? We do not process any transactions over the weekend. Is it necessary to do a DBCC SHRINKFILE on a periodic basis? Please advise.

SQL-SERVER-DB-BACKUP-Error-Messa.bmp (3.0 MB) (File Type Details)

Error 3041 Backup Failed

03.31.2008 at 10:00AM PDT, ID: 21247147

Rank: Guru

VMontalvao:

No. You need to backup transaction logs as well. In Mantienance Plans add the transactions logs backups (at least once by day).

03.31.2008 at 10:14AM PDT, ID: 21247253

cashsistla:

Thanks a lot. I will follow your suggestion and do a TL Backup every night after the DB backup is completed. Will let you know if this solves my problem.

03.31.2008 at 10:29AM PDT, ID: 21247366

cashsistla:

I have one more question please. When I looked at the error logs from last nights backup, I found the following message:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'ATLSRV04' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 3/31/2008 1:00:03 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'ASCTrac'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database ASCTrac: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports... 0 file(s) deleted.

End of maintenance plan 'DB Maintenance Plan1' on 3/31/2008 2:15:27 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

How do I log in in the "Single User Mode"? Normally at nights no users are logged on to this application.

04.01.2008 at 01:59AM PDT, ID: 21252263

sqlconsumer:

Its possible that you may have a rogue lock on one of your databases, check this out in SQL agent.

Also be careful not include DB's using simple recovery model in your transaction log backups it will cause the job to fail.

04.01.2008 at 02:50AM PDT, ID: 21252456

Rank: Guru

VMontalvao:

Looks like you are trying to do a DBCC CHECKDB with repair method. Edit your command and erase the repair part. You won't need it. You would need it if you have some kind of problem in DB.

04.01.2008 at 09:07AM PDT, ID: 21255337

cashsistla:

Thanks a lot for all your help. I am unable to do Transaction Log backups, because I am not logged in "Single User Mode". I tried to run the SQLSERVR.exe -c -m but that did not work. Can somone show me how to log to SQL Sevrver in Single User Mode, and after completing the TL backups put it back in the regular production mode.

04.01.2008 at 10:39AM PDT, ID: 21256265

Rank: Guru

VMontalvao:

You don't need to be in Single User Mode to backup transaction logs.
Anyway, Single User Mode it's an option for databases not for Instances.

04.02.2008 at 10:07AM PDT, ID: 21265091

cashsistla:

I set up the Maintenance Plan to do TL backups and DB backups. Both ran OK last night. But this morining when I did a DBCC SQLPERF(LOGSPACE) I found that the size of the log was 10GB up from 1 GB yesterday and occupied 99% of space. Is there a reason why it grew to such a large size. Do I have to run the DBCC SHRINKFILE every day. How do I handle this on a daily basis. Thanks for all the help.

04.02.2008 at 10:13AM PDT, ID: 21265134

Rank: Guru

VMontalvao:

You might have some job running after backups. Maybe REINDEX job (optimization in Maintenance Plan) or some kind of big data process (Import, Purge, ...).
I'll sugest you to have a different schedule for transaction log backups. Something like backing up TL in each 6 hours (so will be 4 times by day).

04.02.2008 at 10:15AM PDT, ID: 21265157

cashsistla:

When I run the DBCC SHRINKFILE command, I am getting the attached error message. The TL Log files are on the D drive in a folder called Data and name is ASCTrac_Log.LDF

DBCC-SHRINKFILE-ERROR.bmp (3.0 MB) (File Type Details)

DBCC SHRINKFILE ERROR

04.03.2008 at 02:27AM PDT, ID: 21270760

Rank: Guru

VMontalvao:

Verify if the name's correct. For that you can use this command:
select * from sysfiles

If you want you can use FILEID instead of name.

04.03.2008 at 09:41AM PDT, ID: 21274396

cashsistla:

I hope this is the last time I bother you. I am attaching a screen shot of the command you asked me to execute. These are located on the D-drive where the SQL DB is located. Is this correct.
One more question. I went into the ALL TASKS/Maintenance and changed the time for the TL Backups to run at 4 PM. In the past it was set up to run before the DB backups were run around midnight. However, the TL backups ran at 4 PM and again at midnight, and the size of the LogFile increased to 13 GB. Am I missing something here. When the TL backup ran at 4PM its size was only 1.9 GB but when it ran again at midnight it increased to 13 GB. It seems as though when I go into the Maintenace and make changes it does not remove the old schedule, but adds the new time. How do I correct this. Thanks a lot for your help.

Select-star-from-SYSFILES.bmp (3.7 MB) (File Type Details)

Select star from SYSFILES

04.03.2008 at 10:06AM PDT, ID: 21274629

Rank: Guru

VMontalvao:

Like I said before, there's some process that runs at night and handles with huge amount of data.
If transaction log isn't important for you, you can set database to simple mode and stop backing up transaction logs.

NOTE: You can run this command DBCC SHRINKFILE(2, 2) to shrink log

04.03.2008 at 05:43PM PDT, ID: 21278213

cashsistla:

I still do not understand why I am not able to change the Maintenance Plan. I had originally set the TL backup to run at midnight 2 hours before the DB backup starts. I then changed the time to backup the TL to 4 PM instead of 12 AM. But the backup now runs at 4 PM and then at 12 AM. I do not understand why this is happening. What I am doing wrong. Is there a different way to edit the Maintenance Plan other than going back and redoing it iwth the changed schedule?

04.04.2008 at 02:40AM PDT, ID: 21280038

Rank: Guru

VMontalvao:

The only thing I can remember it's that you have 2 schedules for transaction log backup.
You can delete Maintenance Plan and check that all jobs was deleted. Then create a new Maintenance Plan.

04.04.2008 at 02:38PM PDT, ID: 21285818

cashsistla:

I tried what you suggested, but I get the following error message in any statement I use with the term "SYSMAINTPLAN_SUBPLANS"

Select-star-from-SYSMAINTPLAN.bmp (3.0 MB) (File Type Details)

SELECT STAR FROM SYSMAINTPLAN

04.07.2008 at 01:42AM PDT, ID: 21295153

Rank: Guru

VMontalvao:

That's because you are working on wrong database. Should be MSDB.
But be careful because sysmaintplan_subplans it's a SQL Server 2005 object no SQL Server 2000.
Tables that exists in SQL Server 2000 are:
sysdbmaintplans
sysdbmaintplan_jobs
sysdbmaintplan_history
sysdbmaintplan_databases

04.07.2008 at 09:15AM PDT, ID: 21298284

cashsistla:

Thanks a lot for all your inputs and suggestions. I have now deleted all the old Mintenance Plans and have been running a Tran Log Backup and DB backup once every day. Even though the Tran Log backups ran properly, this morning it showed that 98% of the space used. The TL backups ran at 4PM Sunday evening, and the DB backups ran at 4 AM Monday morning. Normally there are no transactions processed over the weekend. How can I reduce the Log Space Used?