SQL Server Home
sys.dm_tran_active_transactions transaction_state not updated when an Attention event occurs
5/7/2009 8:27:26 AM
User(s) can reproduce this bug
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.
SQL Server 2008 - Developer Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
create table x1 (y int)
--record this for later
from master..spt_values a, master..spt_values b, master..spt_values c
--let this run for a while. Meanwhile, check window #2--
from sys.dm_tran_session_transactions st
join sys.dm_tran_active_transactions at on at.transaction_id = st.transaction_id
st.session_id = 53 --SPID from window #1
transaction_state in window #2 will be 2, for "active". After a minute or so, go back to window #1 and hit the Stop button (thereby triggering an Attention event). Now go back to window #2 and watch the transaction_state, which will stay at 2 for the entire duration of the rollback -- as though the rollback isn't even happening.
transaction_state stays at 2
transaction_state should change to 7
to post a comment.
Please enter a comment.
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.
Kevin Cox SQL Server
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.
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.
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.
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.
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...
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.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft