Advertisement

Find active transactions by duration

Share the blog:

Find active transactions by duration

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 Ian_Stirk@yahoo.com.

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.

Solution

Here is a simple TSQL to simplify the work:

Query

Steps

To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

Result

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.

Solution

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

Here is a simple TSQL to simplify the work:

Query

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
Share the blog:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Advertisement