//
you're reading...
SQL Server, T-SQL

Quick & Dirty – Blocking Query Resolution

This technique should be used in extreme emergencies only. It involves identifying the lead blocker and killing his session. Killing transactions can be damaging to your server, so do not use this method without fully understanding the ramifications of what you are doing; there are other (and safer) ways. Lets say that there is an open transaction of some kind that is blocking other transactions and is causing havoc with your production environment. You want to stop the lead blocker and release its locks. Again use with caution, if it is an extreme emergency and time is of the essence then here’s what you can do:

Here is the script we used to identify the blocking query.

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id =tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

The results should look something like the following:

clip_image001

Be sure to evaluate the blocking text to understand what it is your are about to kill. Then it’s a simple matter of executing a kill command against that SPID (KILL 52)

Bonn Appetit!

L.Dixon Galler

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: