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

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

SQL

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

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

SQL

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.”