Find active transactions by duration or space


Recently frequently we faced blocking issues on our production server. The main reason that I understand, recently we merged a few SQL servers and migrated all databases to a single server. Few databases are shared database which is used by some common applications. For me easiest way to find blocking is to run the mentioned script in this post > ‘Find blocking by T-SQL Queries’.

Once we find the blocking SPID I used to check a few things before making the decision to KILL the SPIDs. I personally use ‘dbcc inputbuffer(<spid>)’  to find the query or the script. But for a few days I have some interest to know more information about the blocking SPIDs. I started googling and found the below post.

What SQL Statements Are Currently Using The Transaction Logs?

Credits: Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant specializing in SQL Server in London England. He can be contacted at

To find SQL statements currently using the transaction logs and active transactions by duration or space below script can be used. In the TSQL script author used five Dynamic Management Views (DMVs), and one Dynamic Management Function (DMF) to collect the required information.

The output shows each active transaction in each log. For each line, it shows:

  • How long the transaction has been running
  • The amount of log space used
  • The state of the transaction
  • The current specific piece of SQL code running
  • The routine (stored procedure or batch) that contains the currently running SQL
  • Other interesting information (user, hostname, spid, database name, etc) that is helpful

The output is sorted by transaction duration, but it might also be useful to sort it by log space used.

Note: a given spid may have transactions in several databases.


Now we have created a user-stored procedure on a database and execute the same to get a proper report.

Now to check SQL statements are currently using the transaction logs and active transactions by duration or space simply execute the Usp_Lock and get the report. It seems to be cool for me.

“Please let us know if there are any changes/modifications required. Feel free to comment below – We always appreciate hearing your tips.”

Facebook Comments Box


Leave a Reply

Your email address will not be published.