“Backup and Restore Events” standard database report do not show all backup failure events
While investigating backup failure related issue on SQL SERVER 2008 R2 I was trying to pull the backup failure related information using “Backup and Restore Events” report however interestingly I don’t see any failure reported there.
I went back and tried to reproduce the issue by taking a 10 GB database backup on 5 GB drive which failed with following error message:
Msg 3271, Level 16, State 1, Line 3
A nonrecoverable I/O error occurred on file "f:\test.bak:" 112(There is not enough space on the disk.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
In SQL SERVER errorlog following message recorded however interestingly report unable to pull this failure information:
2015-06-26 19:25:23.770 spid57 Error: 18210, Severity: 16, State: 1.
2015-06-26 19:25:23.770 spid57 Extend Disk Backup: failure on backup device 'f:\test.bak'. Operating system error 112(There is not enough space on the disk.).
2015-06-26 19:25:23.770 Backup Error: 3041, Severity: 16, State: 1.
2015-06-26 19:25:23.770 Backup BACKUP failed to complete the command BACKUP DATABASE sqlnexus. Check the backup application log for detailed messages
Done further research and found that “Backup and Restore Events” report pull backup failure related events from default trace using below query:
INSERT INTO @temp_trace
SELECT Error
,StartTime
,HostName
,ApplicationName
,LoginName
,Severity
,DatabaseName
,TextData
FROM::fn_trace_gettable(@base_tracefilename, DEFAULT)
WHERE substring(TextData, 20, 15) LIKE '%Backup%'
AND TextData LIKE '%Error%'
AND ServerName = @@servername;
Ran above query using SQL SERVER Management Studio however I don’t see any backup failure events. Also reviewed default SQL SERVER trace carefully which does capture “Audit Backup/Restore” and “ERRORLOG” EventClass however still I don’t see this message logged in ERRORLOG. So now it’s clear that it’s not a report issue but a default trace issue which does not capture backup failure events.
I have saved the default trace as a template and then exported the definitions. Later captured profiler trace using same definition manually and this time I see backup failure related events. So looks like SQL Server engine have a check on certain error message and only write selected severity messages.
Done further research on this and found that if the ERROR severity is greater than 18 only in that case we log event information in default trace else SQL skip it.
Well the idea is not to bloat default trace with every backup failure error message which may increase the default trace size significantly. Following article talk about severity 18 and 19 error message: https://msdn.microsoft.com/en-IN/library/ms164086.aspx
Snippet
| 18 | Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs. |
| 19 | Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log. |
If you are interested to capture all backup\restore failure error message in that case you have capture profiler trace separately and then write query to pull the information.
Apart from that you could also run below query to pull backup\restore related events from SQL SERVER ERRORLOG and build your alert accordingly:
XP_READERRORLOG 0, 1,'backup'
GO
XP_READERRORLOG 0, 1,'restore'
Vikas Rana (@vikasrana_dba)
Support Escalation Engineer
Microsoft India GTSC