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 (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s