LLI Wiki — My Digital Knowledge Base

Kill low-priority blocking sessions tagged with CONTEXT_INFO

T-SQL script that finds blocking sessions tagged with CONTEXT_INFO and prints or runs the KILL statements, in print-only mode by default.

This script finds the blocking sessions whose CONTEXT_INFO value marks them as low priority, then prints (or runs) the KILL statements that end them. The marker 0xdba911 is arbitrary: the application or job that should be treated as low priority sets its session's CONTEXT_INFO to this value.

set nocount on;
set deadlock_priority low;
declare @sqlcmd varchar(max);
declare @debug bit; -- 1=print out kill command, 0=execute kill command

set @debug = 1; -- 1=print, 0=exec
set @sqlcmd='';

; with cte (Session_id, Context_info) as
(
select r1.session_id, r1.context_info from sys.dm_exec_requests r1 with (nolock)
inner join sys.dm_exec_requests r2 with (nolock)
on r1.session_id = r2.blocking_session_id 
where r1.session_id > 50
and r1.session_id <> @@spid
union 
select s.session_id, s.context_info from sys.dm_exec_sessions s with (nolock)
inner join sys.dm_exec_requests r with (nolock)
on s.session_id = r.blocking_session_id
and r.session_id <> @@spid
)
select @sqlcmd = @sqlcmd + 'kill ' + cast(session_id as varchar) +';' + char(0x0d) + char(0x0a) from cte
where context_info = 0xdba911; -- 0xdba911 for labelling low priority sessions
if @debug = 1
 print @sqlcmd;
else
 exec (@sqlcmd);

How it works

Notes

With @debug = 0, the blocking sessions are killed immediately. Review the printed list in debug mode first.

Mastodon LLIMastodon SocialFosstodon