3

I have an instance of SQL server that is regularly getting errors like this:

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?

2

You can capture backup events using SQL Server Audit.

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);
GO

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:

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...

| improve this answer | |
  • This looks great, thanks. We run standard edition, so I'd have to use the trace. When I've read about traces, they warn to be careful because you could impact performance if you don't know what you are doing. Is that much of a risk in this case? – Eugene M Feb 26 '13 at 15:00
  • 1
    Profiler is usually the problem, not a server-side trace. That said, you could capture fewer columns, I just scripted them all. They're documented under @columnid here : msdn.microsoft.com/en-us/library/ms186265.aspx – Aaron Bertrand Feb 26 '13 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.