Kevin3NF

In my Top 10 SQL Server Functions post awhile back, I listed DBCC OPENTRAN as one of the top 3, and for good reason.

An Open transaction may simply be something that has not finished yet, or someone issued a BEGIN TRAN without a corresponding COMMIT or ROLLBACK.  Or as we will see at the end, replication is having issues.

You can use this against any database with minimal syntax and get back solid information very quickly.

 
--connect to sample db
use MyDatabase
go

--as generic as this command gets and still runs:
DBCC OPENTRAN
 

Result if nothing is open:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I start and execute a DML (insert, update or delete) transaction with BEGIN TRAN and leave out the corresponding COMMIT, I get:

Transaction information for database ‘SmallData_BigLog’.

Oldest active transaction:
SPID (server process ID): 64 <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:4620:1)
Start time : Apr 1 2017 4:59:02:307PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, if I open a second transaction (in a new query window) and execute any DML statement without the COMMIT, and then run DBCC OPENTRAN again, I get:

Transaction information for database ‘SmallData_BigLog’.

Oldest active transaction:
SPID (server process ID): 64 <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:4620:1)
Start time : Apr 1 2017 4:59:02:307PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Yes…the same output, as this is just showing the ONE oldest transaction.

I can run a query to show that there are two SPIDs with open transactions:

--
SELECT spid, blocked,[dbid],last_batch,open_tran
FROM master.sys.sysprocesses
WHERE open_tran <> 0
 

If I COMMIT spid 64 and re-run DBCC OPENTRAN, the SPID changes to the second transaction I started:

Transaction information for database ‘SmallData_BigLog’.

Oldest active transaction:
SPID (server process ID): 52  <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:9603:1)
Start time : Apr 1 2017 5:11:20:830PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I COMMIT spid 52 and re-run DBCC OPENTRAN along with checking sysprocesses for open_tran <> 0 I get:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

spid blocked dbid last_batch open_tran
—— ——- —— ———————– ———

(0 row(s) affected)

Now, all of that was just running DBCC OPENTRAN by itself.  There are additional options:

--specify dbname, dbid or 0 for the current database
DBCC OPENTRAN (SmallData_BigLog)

You will get results in the same format as the previous examples.

You can suppress all messages, regardless of if a transaction is open or not (but I have no idea why this would help you…)

DBCC OPENTRAN (0) with no_infomsgs

Result:

Command(s) completed successfully.

If you needed to periodically capture the oldest transaction, in order to review later, use WITH TABLERESULTS:

-- TableResults only shows the oldest open tran
-- useful running in a loop to load the oldest
-- tran over time.

--create a temp table
CREATE TABLE #OpenTranStatus (
ActiveTransaction varchar(25),
Details sql_variant
);

-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
EXEC ('DBCC OPENTRAN (SmallData_BigLog) with tableresults')
SELECT * FROM #OpenTranStatus
DROP TABLE #OpenTranStatus
   

In the above, you could create a user table instead of a temp table of course…it depends on your needs.

One more particularly useful item you may see when running DBCC OPENTRAN by itself:

Transaction information for database ‘Music’.

Replicated Transaction Information:
Oldest distributed LSN : (37:143:3)
Oldest non-distributed LSN : (37:144:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If your database is participating in Replication as a Publisher, this may show up when running OPENTRAN, but it doesn’t necessarily mean that the transaction is actually open.  I set this up and stopped the Replication Log Reader and Distribution agent jobs.   I then added some data to a published table (article) and ran DBCC OPENTRAN to get the above result.  Note that there are two lines with LSN information in them (no SPIDs)

I then ran the Log Reader Agent job and got back:

Transaction information for database ‘Music’.

Replicated Transaction Information:
Oldest distributed LSN : (37:157:3)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I have verified that new records I inserted have been read by the log reader, AND distributed to the subscriber(s).  This means that while you are seeing

Oldest distributed LSN : (37:157:3)

There is not an error…just info.

If you have non-distributed LSNs, there is something to troubleshoot in the replication process which is way outside the scope of this post.  A non-distributed replicated transaction/LSN CAN cause some huge Log file growth, and need to be investigated.  If this happens frequently, use the TABLERESULTS option to log to a regular table and alert on it.

Hopefully this gives you some insight into various ways to use DBCC OPENTRAN as well as use cases for the various options.  90% of the time I run this, it is due to application transactions timing out, or log file growth issues.

I love comments….please feel free to leave questions for me in them on this topic.

Thanks for reading!

Kevin3NF

My Pluralsight course for new SQL Server DBAs

Follow @Dallas_DBAs