Finding Open Transactions

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.

Related Posts

Get Windows Failover Cluster Errors

John Morehouse walks us through the Get-ClusterLog cmdlet in Powershell: Sometimes you know that a problem occurred, but the tools are not giving you the right information.  If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen.  The user interface won’t show you any errors, but you KNOW […]

Read More

SQL Undercover Inspector V1.3

Adrian Buckman announces a new version of the SQL Undercover team’s Inspector: We know some of you really hate linked servers so we have been working on a powershell collection which will allow you to install the inspector without using linked servers to centrally log the information and instead the powershell function Invoke-SQLUndercoverInspector will do […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031