Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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