5

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!

  • This is a good question. My initial thought is XEvent may help, and indeed, I found this link sqlscope.wordpress.com/2012/04/09/…, it may be of some help. But a more comprehensive solution will very likely include SQL:StmtStarting, SQL:StmtCompleted, and group them by session_id and see whether there is missing pair of StmtCompleted for StmtStarting, together with the rollback event to figure out the rollback at statement level. – jyao Nov 30 '16 at 19:13
  • I think Extended Events would not help for me. Judging from the link you provided I would need SQL Server 2012 onwards. I guess I could use it as extra reason for pushing that upgrade! :-) – paulH Dec 2 '16 at 12:30
  • Check .. sp_SQLskillsAbortedTransactions.sql - This script cracks the transaction log and shows which transactions were rolled back after a crash --> Written by Paul S. Randal, SQLskills.com – Kin Shah Apr 6 '17 at 17:05
  • No firsthand experience, but maybe you see user rollbacks issued by a connection Pool which resets connections when they are returned to the pool (and not checking the transaction level).that would explain why no real rollbacks show up in the log. – eckes Nov 23 '17 at 21:59
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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