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]