Validus … talkin bout <code> n sh!t

How to check for active transactions

Debugging an issue today at work, and needed to find if there was any uncommitted transactions or active transactions.

There's a couple of ways to do this:

SELECT *
FROM sys.dm_tran_session_transactions

Thanks Pinal Dave - more from his blog post here.

Also you can use dbcc opentran Or SELECT @@TRANSCOUNT

So, if you're like me and what to see this in action, try the following script.

Assuming you have the same table or just create it, or use you're own.

-- Test to make an uncommited transaction
begin tran
  UPDATE Users
  SET Surname = 'Dalyy'
  WHERE ID = 1
--commit tran

This will execute and take it's sweet time. Then run dbcc opentran and it will return something like this:

Transaction information for database 'Test'.

Oldest active transaction:
    SPID (server process ID): 51
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (9360:4061:4)
    Start time    : Nov 23 2011  9:38:18:980AM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

No related posts.