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.
Here is a simple TSQL to simplify the work:
Select * FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat ON tst.transaction_id = tat.transaction_id INNER JOIN sys.dm_tran_database_transactions AS tdt ON tst.transaction_id = tdt.transaction_id INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON tst.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
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
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.
Here is a simple TSQL to simplify the work:
USE < DATABASE name > -- Use the databse name where the user stored procedure will be create GO CREATE PROC Usp_Lock -- Creating a user stored procedure AS BEGIN -- Purpose: Report active transactions by space or duration. -- Author: I. Stirk. -- Do not lock anything, and do not get held up by any locks. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- What SQL statements are currently using the transaction logs? SELECT tst.session_id ,es.original_login_name ,DB_NAME(tdt.database_id) AS DatabaseName ,DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)] ,tdt.database_transaction_log_record_count AS SpaceUsed ,CASE tat.transaction_state WHEN 0 THEN 'The transaction has not been completely initialized yet' WHEN 1 THEN 'The transaction has been initialized but has not started' WHEN 2 THEN 'The transaction is active' WHEN 3 THEN 'The transaction has ended' WHEN 4 THEN 'The commit process has been initiated on the distributed tran' WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution' WHEN 6 THEN 'The transaction has been committed' WHEN 7 THEN 'The transaction is being rolled back' WHEN 8 THEN 'The transaction has been rolled back' ELSE 'Unknown' END AS TransactionState ,SUBSTRING(TXT.TEXT, (er.statement_start_offset / 2) + 1, ( ( CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), TXT.TEXT)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset ) / 2 ) + 1) AS CurrentQuery ,TXT.TEXT AS ParentQuery ,es.host_name ,CASE tat.transaction_type WHEN 1 THEN 'Read/Write Transaction' WHEN 2 THEN 'Read-Only Transaction' WHEN 3 THEN 'System Transaction' WHEN 4 THEN 'Distributed Transaction' ELSE 'Unknown' END AS TransactionType ,tat.transaction_begin_time AS StartTime FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat ON tst.transaction_id = tat.transaction_id INNER JOIN sys.dm_tran_database_transactions AS tdt ON tst.transaction_id = tdt.transaction_id INNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON tst.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT --ORDER BY tdt.database_transaction_log_record_count DESC -- log space size. ORDER BY [TransDuration(s)] DESC -- transaction duration. END
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.”
Leave a Reply