Andy Mallon has a stored procedure to find open transactions:
I’m fussy. Paul’s example script is awesome, but I want to tweak it to meet my needs. I also want to wrap it into a stored procedure so that I can put it in my DBA database, and have it installed on every server I manage. In addition to the columns Paul pulls back, I want to find out:
- More session details: host name, application name, etc
- More transaction info: Transaction state, duration, etc
- Filter out very short transactions: I want to filter out the noise for regular activity
There’s also a transaction state that I exclude. Transaction State 3 means “The transaction has been initialized but has not generated any log records.” A transaction in this state won’t affect log reuse. However, it could affect the size of the version store in TempDb if you’re using Read Committed Snapshot Isolation. I haven’t run into this as a problem, so I filter it out.
I used dbo.Check_Blocking as a base to create another check script to check open transactions
Click through to see what Andy’s procedure offers and also for a link to get the script.