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.
Best Answer
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:
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.