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.
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 tstINNER JOIN sys.dm_tran_active_transactions AS tat ON tst.transaction_id = tat.transaction_idINNER JOIN sys.dm_tran_database_transactions AS tdt ON tst.transaction_id = tdt.transaction_idINNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_idINNER JOIN sys.dm_exec_requests er ON tst.session_id = er.session_idCROSSAPPLY 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<DATABASEname>-- Use the databse name where the user stored procedure will be createGOCREATEPROC Usp_Lock -- Creating a user stored procedureASBEGIN-- Purpose: Report active transactions by space or duration. -- Author: I. Stirk. -- Do not lock anything, and do not get held up by any locks. SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED-- 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_stateWHEN0THEN'The transaction has not been completely initialized yet'WHEN1THEN'The transaction has been initialized but has not started'WHEN2THEN'The transaction is active'WHEN3THEN'The transaction has ended'WHEN4THEN'The commit process has been initiated on the distributed tran'WHEN5THEN'The transaction is in a prepared state and waiting resolution'WHEN6THEN'The transaction has been committed'WHEN7THEN'The transaction is being rolled back'WHEN8THEN'The transaction has been rolled back'ELSE'Unknown'ENDAS TransactionState ,SUBSTRING(TXT.TEXT, (er.statement_start_offset /2) +1, ( (CASEWHEN er.statement_end_offset =-1THENLEN(CONVERT(NVARCHAR(MAX), TXT.TEXT)) *2ELSE er.statement_end_offsetEND- er.statement_start_offset ) /2 ) +1) AS CurrentQuery ,TXT.TEXT AS ParentQuery ,es.host_name ,CASE tat.transaction_typeWHEN1THEN'Read/Write Transaction'WHEN2THEN'Read-Only Transaction'WHEN3THEN'System Transaction'WHEN4THEN'Distributed Transaction'ELSE'Unknown'ENDAS TransactionType ,tat.transaction_begin_time AS StartTimeFROM sys.dm_tran_session_transactions AS tstINNER JOIN sys.dm_tran_active_transactions AS tat ON tst.transaction_id = tat.transaction_idINNER JOIN sys.dm_tran_database_transactions AS tdt ON tst.transaction_id = tdt.transaction_idINNER JOIN sys.dm_exec_sessions es ON tst.session_id = es.session_idINNER JOIN sys.dm_exec_requests er ON tst.session_id = er.session_idCROSSAPPLY 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.”