set context_info 0xdba911 -- arbitrary, and can be any value you like

세션1

세션2

세션3

if object_id('dbo.t', 'U') is not null

drop table dbo.t;

create table dbo.t (a int, b varchar(30));

go

insert into dbo.t (a, b) values (1, 'hello')

go

set context_info 0xdba911; -- change 0xdba911 to your preferred value

begin tran

update dbo.t set b= 'world' from dbo.t where a=1

waitfor delay '00:05:00' -- wait for 5 min

commit tran;

delete from dbo.t where a=1;

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);