SQLTeam.com | Weblogs | Forums

Killing blocking spid


#1

Hello All,
I need to automatically kill the blocking process when it happens. I have the following query to find the spid.
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

Now I need to write a query to kill the spid automatically which was in my result of the qyery. how would I write that? I want to write the query and run it as a job periodically. Please help!


#2
DECLARE @spid varchar(20) = 0;
SELECT
      @spid = wt.blocking_session_id
   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;
DECLARE @sql varchar(1000)= 'BEGIN TRY
    KILL ' + @spid + ';
END TRY
BEGIN CATCH
   PRINT Error_Message();
END CATCH';
EXEC ( @sql );
GO

#3

Thank you so much!