snippetMinor
How to tell what's failing at making backups?
Viewed 0 times
backupswhatfailingtellhowmaking
Problem
I have an instance of SQL server that is regularly getting errors like this:
and this:
According to our backup administrator, our Data Protector backups are A) running at a different time and B) running successfully. If that's the case, then I've got some rogue service attempting backups and failing.
Either way, I need figure out what's causing the errors. So, is there any way to track or log what entities are trying to make backups?
02/25/2013 23:22:38,spid138,Unknown,BackupVirtualDeviceFile::SendFileInfoBegin:
failure on backup device '{E0EA0FA7-D8FA-401B-8B4D-7C479F32A35D}21'.
Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).and this:
02/25/2013 23:22:38,Backup,Unknown,BACKUP failed to complete the command BACKUP DATABASE master.
Check the backup application log for detailed messages.According to our backup administrator, our Data Protector backups are A) running at a different time and B) running successfully. If that's the case, then I've got some rogue service attempting backups and failing.
Either way, I need figure out what's causing the errors. So, is there any way to track or log what entities are trying to make backups?
Solution
You can capture backup events using SQL Server Audit.
Then you can view the Audit Logs from within SSMS (Security > Audits).
If you can't use audit (e.g. due to edition restrictions), you can use a server-side trace:
I've probably captured more columns there than you need...
USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);
GO
CREATE SERVER AUDIT SPECIFICATION BackuAudit
FOR SERVER AUDIT ServerAudit
ADD (BACKUP_RESTORE_GROUP);
GO
ALTER SERVER AUDIT SPECIFICATION BackuAudit
WITH (STATE = ON);
GOThen you can view the Audit Logs from within SSMS (Security > Audits).
If you can't use audit (e.g. due to edition restrictions), you can use a server-side trace:
declare @rc int, @TraceID int, @maxfilesize bigint = 5;
exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;
exec sp_trace_setevent @TraceID, 115, 1, 1;
exec sp_trace_setevent @TraceID, 115, 9, 1;
exec sp_trace_setevent @TraceID, 115, 3, 1;
exec sp_trace_setevent @TraceID, 115, 4, 1;
exec sp_trace_setevent @TraceID, 115, 5, 1;
exec sp_trace_setevent @TraceID, 115, 6, 1;
exec sp_trace_setevent @TraceID, 115, 7, 1;
exec sp_trace_setevent @TraceID, 115, 8, 1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;
exec sp_trace_setstatus @TraceID, 1;
select TraceID = @TraceID;I've probably captured more columns there than you need...
Code Snippets
USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);
GO
CREATE SERVER AUDIT SPECIFICATION BackuAudit
FOR SERVER AUDIT ServerAudit
ADD (BACKUP_RESTORE_GROUP);
GO
ALTER SERVER AUDIT SPECIFICATION BackuAudit
WITH (STATE = ON);
GOdeclare @rc int, @TraceID int, @maxfilesize bigint = 5;
exec sp_trace_create @TraceID output, 0, N'c:\temp\backup_trc', @maxfilesize, NULL;
exec sp_trace_setevent @TraceID, 115, 1, 1;
exec sp_trace_setevent @TraceID, 115, 9, 1;
exec sp_trace_setevent @TraceID, 115, 3, 1;
exec sp_trace_setevent @TraceID, 115, 4, 1;
exec sp_trace_setevent @TraceID, 115, 5, 1;
exec sp_trace_setevent @TraceID, 115, 6, 1;
exec sp_trace_setevent @TraceID, 115, 7, 1;
exec sp_trace_setevent @TraceID, 115, 8, 1;
exec sp_trace_setevent @TraceID, 115, 10, 1;
exec sp_trace_setevent @TraceID, 115, 11, 1;
exec sp_trace_setevent @TraceID, 115, 12, 1;
exec sp_trace_setevent @TraceID, 115, 14, 1;
exec sp_trace_setevent @TraceID, 115, 21, 1;
exec sp_trace_setevent @TraceID, 115, 23, 1;
exec sp_trace_setevent @TraceID, 115, 26, 1;
exec sp_trace_setevent @TraceID, 115, 28, 1;
exec sp_trace_setevent @TraceID, 115, 29, 1;
exec sp_trace_setevent @TraceID, 115, 34, 1;
exec sp_trace_setevent @TraceID, 115, 35, 1;
exec sp_trace_setevent @TraceID, 115, 37, 1;
exec sp_trace_setevent @TraceID, 115, 40, 1;
exec sp_trace_setevent @TraceID, 115, 41, 1;
exec sp_trace_setevent @TraceID, 115, 49, 1;
exec sp_trace_setevent @TraceID, 115, 50, 1;
exec sp_trace_setevent @TraceID, 115, 51, 1;
exec sp_trace_setevent @TraceID, 115, 60, 1;
exec sp_trace_setevent @TraceID, 115, 64, 1;
exec sp_trace_setstatus @TraceID, 1;
select TraceID = @TraceID;Context
StackExchange Database Administrators Q#35457, answer score: 2
Revisions (0)
No revisions yet.