dbcc opentran detect all open trans?

Question

what command or tool can tell all open transactions in a database? "dbcc opentran" does it?, the SQL CHECK tool always reporting an spid having opentran, but when run the dbcc opentran(dbid) doesn't have anything

Answer 1

DBCC OPENTRAN(dbid)  only reports on a transaction that is open in the database in question.  However, a transaction can open in another database but still holding locks in the dbid you checked.   In that case, you will not see the transaction.  You have to find the proper database to check.

You might find this script from Paul Randal helpful: http://www.sqlskills.com/BLOGS/PAUL/post/Script-open-transactions-with-text-and-plans.aspx

RLF

Answer 2

From the master database use

Select * from sysprocesses where open_tran>0

---

"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

Answer 3

thank you both EricHason and Russell, I will mark your reply as answers later, by both advices, there are open trans displayed as SQLCheck, and mentioned the dbid which I checked with dbcc opentran(dbid) still has no open transactions returned.

how bad is an session hold open transaction? by looking at cpu, memory etc?

Answer 4

A long term open transaction is, of course, a greater source of serious blocking than a quick transaction.  How long has the transaction been open?  It is possible for a long running job to begin and commit transactions hundreds or thousands of times in a big task.  

So, the question "How long has this transaction been open?" is more important than "Why does this task always seem to have an open transaction?"

You most likely notice transactions when they block some other process.  Again, the frequency and the length of the block make the difference between transactional integrity and a block that hampers other users.

Neither CPU nor memory tell you anything about transactions.  In fact, the worst open transaction is one where the client is sleeping.  In that case, until either the client or the sysadmin takes action, the transaction will remain open.

If you prefer DBCC OPENTRAN(dbid)  you can create a script to run that on every database on your server.  That would allow you to see all of the DBCC Results:

DECLARE @SQLScript NVARCHAR(MAX)
SELECT @SQLScript = ISNULL(@SQLScript,'') + 'DBCC OPENTRAN('+ Name +');'
FROM sys.databases
EXEC (@SQLScript)

If a transaction is open, the name of the database will be reported along with the open transaction.

RLF