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
- Blocking sessions are found with
sys.dm_exec_requests(blocking_session_id) andsys.dm_exec_sessions. - System sessions (session ID 50 or lower) and the current session are skipped.
- Only sessions whose
CONTEXT_INFOequals0xdba911are kept. - With
@debug = 1, the script prints theKILLstatements without running them. - With
@debug = 0, the script runs theKILLstatements.
Notes
With @debug = 0, the blocking sessions are killed immediately. Review the printed list in debug mode first.