Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2011-10-03 : 15:10:51
|
I recently ran the following (SELECT * FROM sys.dm_tran_active_transactions ORDER BY transaction_begin_time) and I see that we have a set of transactions from 2 months ago that are out there uncommitted. One of which is an UPDATE. What can I do to deal with these transactions? The UPDATE transaction type is 1 (read/write), with a state of 2 (active) and a dtc_state of 0...transaction_id name transaction_begin_time transaction_type transaction_uow transaction_state transaction_status transaction_status2 dtc_state dtc_status dtc_isolation_level filestream_transaction_id72151762 UPDATE 2011-10-03 12:54:48.543 1 NULL 2 0 2 0 0 -1 NULL72151777 worktable 2011-10-03 12:54:48.550 2 NULL 2 0 0 0 0 0 NULL72151784 worktable 2011-10-03 12:54:48.550 2 NULL 2 0 0 0 0 0 NULL72151791 sort_init 2011-10-03 12:54:48.550 1 NULL 2 0 514 0 0 -1 NULL72151792 sort_fake_worktable 2011-10-03 12:54:48.550 2 NULL 2 0 0 0 0 0 NULL72151790 sort_fake_worktable 2011-10-03 12:54:48.550 2 NULL 2 0 0 0 0 0 NULL72151793 sort_init 2011-10-03 12:54:48.550 1 NULL 2 0 514 0 0 -1 NULL74819680 topn_fake_worktable 2011-10-03 12:59:09.560 2 NULL 2 0 0 0 0 0 NULL74819681 sort_init 2011-10-03 12:59:09.560 1 NULL 2 0 514 0 0 -1 NULL74821346 SELECT 2011-10-03 12:59:09.753 2 NULL 2 0 0 0 0 0 NULL74821353 sort_fake_worktable 2011-10-03 12:59:09.753 2 NULL 2 0 0 0 0 0 NULL74821354 sort_init 2011-10-03 12:59:09.753 1 NULL 2 0 514 0 0 -1 NULL |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-10-03 : 15:22:29
|
| Whatever you do, don't restart SQL until this is cleaned up. If you do restart SQL, it's going to go into a lengthy crash recovery. We had a transaction that was out there for 9 days, and after a few hours of waiting for crash recovery to complete, we decided to do a restore instead. This was done in conjunction with a support case with Microsoft. I would recommend killing the transaction after you've grabbed all of the needed information from the spid so that you can investigate why this occurred and prevent it from happening in the future.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Dealing with old transactions