September 22, 2011 at 1:58 pm
My backups have been failing and I noticed the following in the SQL ERRORLOG
2011-09-21 22:13:49.70 Backup Database backed up. Database: ...
2011-09-21 22:13:50.68 Backup Database backed up. Database: ...
2011-09-21 22:13:51.86 Backup Database backed up. Database: ...
2011-09-21 22:29:25.51 Backup Error: 3041, Severity: 16, State: 1.
2011-09-21 22:29:25.51 Backup BACKUP failed to complete the command BACKUP DATABASE XXXXX. Check the backup application log for detailed messages.
There is no other information as to what might be causing this.
Where is the backup application log??
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 2:04 pm
thats a darn good question and I don't know why its been made harder to find out something important such as why a backup failed, all the info should be in the errorlog. I can only think that they mean any associated errors that might be in the errorlog (such as OS error 112) or they mean any log file you might have your backup job writing to (log file from a maintenance plan for example).
Otherwise there is in fact no such thing as a 'backup application log'
---------------------------------------------------------------------
September 22, 2011 at 2:17 pm
Are you using native SQL Server backups or are you running a third party backup application (LiteSpeed, et. al.)?
September 22, 2011 at 2:20 pm
george sibbald (9/22/2011)
thats a darn good question and I don't know why its been made harder to find out something important such as why a backup failed, all the info should be in the errorlog. I can only think that they mean any associated errors that might be in the errorlog (such as OS error 112) or they mean any log file you might have your backup job writing to (log file from a maintenance plan for example).Otherwise there is in fact no such thing as a 'backup application log'
I wanted to say this but didn't originally: makes for a fancy error message with no meaning... 🙂
Very frustrating...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 2:21 pm
Lynn Pettis (9/22/2011)
Are you using native SQL Server backups or are you running a third party backup application (LiteSpeed, et. al.)?
I'm using native backups.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 2:23 pm
Job history?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2011 at 2:33 pm
GilaMonster (9/22/2011)
Job history?
The history log of the failure is not very helpful:
Message
Executed as user: xxxx\xxxx. ..._Content', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 4035) Processed 68 pages for database 'xxxx', file 'xxxx_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 3892612 pages in 713.620 seconds (44.685 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 10720 pages for database 'xxxx', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 4035) Processed 7 pages for database 'xxxx', file 'xxxx_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 10727 pages in 4.367 seconds (20.121 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 12136 pages for database 'xxxx', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 403... The step failed.
However, I had turned on logging to a txt file for the job step, and that gave me the answer.
We are using a routine to prune old backup files before backing up a database, and the pruning step failed because it was unable to delete a backup file that was locked by another process; I am suspecting that our file-system backup is the "other process" that was locking the file.
Lesson from this: turn on job-step logging, as it will provide more info than the SQL ERRORLOG or job history. 🙂
Thanks all for your responses!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 2:51 pm
Marios Philippopoulos (9/22/2011)
GilaMonster (9/22/2011)
Job history?The history log of the failure is not very helpful:
Message
Executed as user: xxxx\xxxx. ..._Content', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 4035) Processed 68 pages for database 'xxxx', file 'xxxx_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 3892612 pages in 713.620 seconds (44.685 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 10720 pages for database 'xxxx', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 4035) Processed 7 pages for database 'xxxx', file 'xxxx_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 10727 pages in 4.367 seconds (20.121 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 12136 pages for database 'xxxx', file 'xxxx' on file 1. [SQLSTATE 01000] (Message 403... The step failed.
However, I had turned on logging to a txt file for the job step, and that gave me the answer.
We are using a routine to prune old backup files before backing up a database, and the pruning step failed because it was unable to delete a backup file that was locked by another process; I am suspecting that our file-system backup is the "other process" that was locking the file.
Lesson from this: turn on job-step logging, as it will provide more info than the SQL ERRORLOG or job history. 🙂
Thanks all for your responses!
so I was right then!
---------------------------------------------------------------------
September 22, 2011 at 3:01 pm
george sibbald (9/22/2011) so I was right then!
Yes! 😉
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 2, 2013 at 12:23 pm
The problem here is the backup file was locked, we need to close the file.
Steps to close the file:
Computer Management-->>System Tools-->Shared Folders-->>Open Files-->(here we can see our back up file)-->>right click-->>Close Open file
Now take backup, if backup doesn't go through then we need stop the services of client scheduler and client Acceptor
Computer Management-->>Services and Applications-->>Services-->>find the client acceptor and scheduler, stop the services go to backup folder and delete the file, start the services back and take a backup
Cheers...!!!
July 2, 2013 at 12:42 pm
Please note: 2 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply