When DBCC OpenTran doesn’t list all open transactions


Recently I was troubleshooting a customer problem on SQL 2000 instance when there was a hanging session that locking tens of sessions on the server. The customer was suspecting that there was an open transaction for days which causes this problem but when using DBCC OpenTran it doesn't show any transaction.

However when checking sysprocesses I see there's a session with open_tran =1. And this behavior is reproducible in SQL 2012 as well so it's not related to SQL 2000. So why DBCC OpenTran doesn't show it?

Well, it's not clear in in the DBCC OpenTran documentation but DBCC OpenTran shows the open transactions by reading the Transaction log and gets the open transactions from it. If the transaction is not listed in the Transaction Log it will not be listed by DBCC OpenTran. In this case the open transaction was a select transaction that it doesn't show up in the transaction log so the DBCC OpenTran didn't list it however you can get it using sysprocesses. Also after troubleshooting this issue I found that the select transaction is using Serializable transaction isolation level which caused lots of range locks which blocked the index maintenance job and in return blocked many other processes.

To reproduce the problem you can do this by opening two sessions to AdventureWorks sample database

P1

Set Transaction Isolation Level Serializable;

go

Begin Tran

Select * from HumanResources.Department;

 

P2

DBCC OpenTran('AdventureWorks2008R2'); --reads from the T-Log

Select * from sys.sysprocesses where open_tran>0;

select * from sys.dm_tran_locks;

 

Also if you are using Simple recovery model you can confirm that P1 didn't leave a trace in the Transaction Log using the undocumented function fn_dblog().

 

 

Comments (0)

Skip to main content