SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN

I recently received a question on my Facebook page-

“Pinal,

How do I know if there is any open transaction in my database? I recently wanted to restart my server, but my manager asked me to first check if there is any open transaction in database before I restart the system.”

Very interesting question. The answer is very simple.

You can execute the following script to see if there is any open transaction. First run following script on any of your development server.

BEGIN TRAN
UPDATE Person.Address
SET AddressLine2 = ''
WHERE AddressID = 112

Now open another connection in MySQL and execute following script.

DBCC OPENTRAN

Above DBCC will return following output where it will return the details for the oldest active transaction. For example, here is the sample output in my system.

Transaction information for database 'AdventureWorks2012'.
Oldest active transaction:
 SPID (server process ID): 57
 UID (user ID) : -1
 Name : user_transaction
 LSN : (163:324:1)
 Start time : Jan 16 2014 4:34:58:813PM
 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are looking for a script where you can get details for all the transactions with text and plan, here is the excellent script from SQLskills team, I use it all the time when I have to work with Active Transactions.

Reference: Pinal Dave (https://blog.sqlauthority.com)