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?