Report rollbacks to log file

Question

There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File Viewer (see screenshot for example). The information about the transaction should be as detailed as possible. I want to use this information for detecting problems with a client who is losing data. I suspect the problem has somethging to do with a rollback. The problem does almost never occur so I can't use a profiler to see what happens.

The question is: how to log all rollbacks to the log file?

The platform is SQL Server 2005, but if this solution is only possible in a newer version then I would like to hear that too.

Answer 1

I cant offer you to dump this to the errorlog. For that you need to do some serious research and both these options are lightweight and can leave you with a simple file to look at.

In SQL Server 2005 you need to create a server side trace on transaction filtering on eventsubclass 2.

In SQL Server 2008R2 and later you are better off creating an extended event session on the SQLSERVER.SQL.TRANSACTION event filtering on transaction_state = 2

But since you are using SQL Server 2005 you need a trace, here is a script that will create a server side trace capturing all rollback events:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically.

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error



-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 50, 11, @on
exec sp_trace_setevent @TraceID, 50, 6, @on
exec sp_trace_setevent @TraceID, 50, 8, @on
exec sp_trace_setevent @TraceID, 50, 10, @on
exec sp_trace_setevent @TraceID, 50, 12, @on
exec sp_trace_setevent @TraceID, 50, 13, @on
exec sp_trace_setevent @TraceID, 50, 15, @on
exec sp_trace_setevent @TraceID, 50, 34, @on
exec sp_trace_setevent @TraceID, 50, 35, @on
exec sp_trace_setevent @TraceID, 50, 60, @on
exec sp_trace_setevent @TraceID, 50, 64, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 2
exec sp_trace_setfilter @TraceID, 21, 0, 0, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

For this to work for you, you will need to setup the trace to start with the server.

For that you need to make sure that the filename is unique each time the trace is started, create a procedure from the trace definition and make it a startup procedure.