sys.dm_tran_active_transactions transaction_state not updated when an Attention event occurs - by Adam Machanic

Status : 


Sign in
to vote
ID 444030 Comments
Status Active Workarounds
Type Bug Repros 30
Opened 5/7/2009 8:27:26 AM
Access Restriction Public


sys.dm_tran_active_transactions has a column called transaction_state that should have a value of 7 when a transaction is rolling back.  

Attention events will cause some in-flight transactions to roll back, but rollbacks due to these events are not reflected in the transaction_state column in the DMV -- the state stays at 2, "active".  This makes it incredibly difficult to debug rollbacks that are occurring due to Attention events.
Sign in to post a comment.
Posted by TechVsLife2 on 7/28/2012 at 4:17 PM
Agreed--it's hard to explain to the check writers that the system is down for hours, but infinitely worse to add that you are not sure when it will be back up or even what it's up to (completion or rollback). There should be some way of providing both the current (or most recent) state and an increment counter, with estimated number of counters to complete current stage -- some kind of progress bar. What we have here is pretty much Mac's spinning beach ball of death.
Posted by kevincox on 7/26/2012 at 8:21 AM
One of the few times I've been verbally abused in a professional environment was by a CIO for a large bank who was going through hours of "downtime" while a large transaction was rolling back on their main transaction table. It was very difficult to inform the cusotmer that patience was the only course of action and there was no information available as to the state of the rollback and the progress it was making. I would love to get several enhancements, the first of which is simple information on the state of a transaction.
Posted by Adam Machanic on 6/26/2012 at 3:23 PM
Re-opening this. I just spent 12 hours waiting for a transaction to complete, only to have it confirmed, after 12 hours, that the transaction log had filled up several hours in.

I very much would have liked to know what the transaction -- an ALTER TABLE on a very large table -- was doing during all of those hours during which it was rolling back. Alas, I had no information at all, and was not even able to tell that the transaction wasn't just taking a long time to complete. This means I was unable to do any kind of planning for hours. Not a good situation to be in, and absolutely a terrible user experience.

Please fix this -- either in the active_transactions DMV, or in some other DMV. It's truly unbelievable to me that there is no way to get this key information when an issue is in flight.
Posted by Microsoft on 11/14/2011 at 3:42 PM
At this time, we do not have plans to implement these changes in rollback reporting.
We are in the process of clearing out the backlog of DCRs to a manageable number, so that the DCRs of merit can be acted upon.
If you feel that this is a highly important scenario, please either reopen or file a new report and we will re-evaluate it for future work.

Kevin Farlee
Posted by Microsoft on 11/6/2009 at 4:23 PM

This behaviour is by design. Each time a statement is run, it starts a virtual scope within the transaction (akin to a nested transaction), which is used to log the changes made by that stement. If an error occurs (such as the attention event), depending on the severity of the exception either the scope will be rolled back or the whole transaction.

The rollback is limited to the scope for low-severity exceptions or if XACT_ABORT is OFF. For high-severity exceptions, the whole transaction will be rolled back.

Because of the above, during the time the scope is rolled back, the transaction itself is not necessarily being rolling back, so that's the reason the state remains as ACTIVE (2) in the DMV.

We will consider adding other monitoring indicators for a future release, which will indicate whether the scope is being rolled back.

I'm opening a DCR for SQL 11 to look at this. Hopefully the Connect item will transfer along.

Kevin Farlee
Posted by Adam Machanic on 10/3/2009 at 7:13 AM

I just discovered that the transaction state is also not updated when the tran log fills up and causes the transaction to roll back. Can these situations be documented? My monitoring strategy for rollbacks is more or less shot at this point...

Posted by Microsoft on 5/18/2009 at 11:15 AM
Thanks for reporting this.
We'll look into correcting this issue for the next major release of SQL Server.