Tracking down a transaction rollback

Question

I have a very busy database and I've been asked to look for transactions that rollback.

I used SQL Server Profiler to monitor the TM: Rollback Tran completed event, which shows me that dozens of these events are happening every minute, but it doesn't give me any indication as to what transaction is being rolled back, what objects are affected by the rollback, or why the rollback occurred. In short, it seems of very limited use.

If I try to also monitor, for example, SQL:StmtStarting to give some context to the rollback then there is simply too much noise on this busy environment to be able to identify which statement is responsible for the rollback.

I also tried running the statement

SELECT *
FROM fn_dblog (NULL, NULL)
WHERE Operation = 'LOP_ABORT_XACT';

but this did not return any results (which seems odd given the number of rollbacks being reported by SQL Profiler).

Is there any way that I can produce a report showing transactions that have rolled back?

TIA!

Answer 1

Here's the pre-Extended Events way to do this.

Instead of using Profiler interactively, use it to generate a Server Side SQL Trace that writes to a file. After running the trace for a bit, you can query the trace files and/or load the trace files into a table and query those. Find a session that has the rollback, and then query for all the events for that session, in order.

Answer 2

I will try something here that may not be the better way to do it and will probably have a performance impact on you server but still, I guess it's better then nothing. In the sys.dm_exec_requests dmv, you have the status of the query. When it rollback, it shows "rollback". If you create a job that constantly look into that DVM and collect the SQL_handle (and more information if required) of any request with that status, that could be a good starting point.