May 6, 2009 at 11:23 am
My development teammate came to me and said that as she tried to close down SSMS, it asked her if she wanted to commit Transactions that were still open. She had been using SSMS for a while and had issues multiple commands so she was not sure which one was still open.
She wanted to know if I could identify the transactions for her and show her the SQL statement and then depending on what it was, roll it back or commit it.
I used DBCC OPENTRAN to identify that there were open transactions. I also found dm_tran_active_transactions and dm_tran_database_transactions to get the TransactionId.
How do I proceed from here? How do I get the SQL statement that caused this issue?
After I find it, how do I do I
a. Commit the transaction
b. Rollback the transaction
In this case, it is easy because it is SSMS - I can issue these commands. What if an app does something like this and leaves an open transaction? How do I issue the COMMIT or ROLLBACK the open transactions then?
May 6, 2009 at 11:50 am
Not sure if you can commit someone else's transaction, as for a rollback you can kill it, that will force a rollback.
As for the last statement try dbcc inputbuffer(spid)
September 8, 2010 at 3:00 am
If you have mentioned "Transaction Name" while opening it then you are able to findout open transactions by following query:
select DB_NAME(database_id), * from sys.dm_tran_active_transactions tc inner join
sys.dm_tran_database_transactions td
on tc.transaction_id = td.transaction_id
You can also check tc.transaction_type column value:
It represents:
Type of transaction.
1 = Read/write transaction
2 = Read-only transaction
3 = System transaction
4 = Distributed transaction
As per the value & database name, you are able to findout the transaction while you want. Still be careful while rollback the transaction so you will not kill others transactions.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply