sqlserver kill long time lock

All:

I have finished a simple program to kill long time lock and passed test,If anyone need it,Pls kindly test it and then deploy it,we can get some part of this to combine with our existing BlockingDetectionMonitor monitor program.Wish it will give us some help.Thanks a lot.

CREATE TABLE [dbo].[save_killed_session] (
[id] [numeric](20, 0) IDENTITY (1, 1) NOT NULL ,
[rq] [datetime] NULL ,
[spid] [int] NULL ,
[status] [nchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[hostname] [nchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[hostprocess] [nchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[loginame] [nchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[program_name] [nchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[open_tran] [smallint] NULL ,
[waittime] [int] NULL ,
[cmd] [nchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[EventType] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[Parameters] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[EventInfo] [varchar] (5000) COLLATE Chinese_PRC_CI_AS NULL
)

CREATE proc LongTimeBlockKill
@max_waittime int = 15000
as
declare @spid smallint
declare @t_01 varchar(20)
declare @status varchar(30)
declare @hostname varchar(128)
declare @hostprocess varchar(8)
declare @loginame varchar(128)
declare @program_name nchar(128)
declare @open_tran smallint
declare @waittime int
declare @cmd varchar(16)
declare @EventType varchar(500)
declare @Parameters varchar(500)
declare @EventInfo varchar(5000)
declare @kill varchar(5000)
declare cur_wait_kill cursor STATIC for
SELECT
spid,status,hostname,hostprocess,loginame,program_name,open_tran,b.waittime,cmd
FROM
master.dbo.sysprocesses a with(nolock),
/*If many sessions were blocked by on session we get the max waittime from them not sum*/
(SELECT blocked,max(waittime) waittime FROM master.dbo.sysprocesses with(nolock)
where blocked>0 AND waittime > @max_waittime
group by blocked) b
WHERE
a.spid = b.blocked
and a.blocked=0
set @kill = ''
create table #SqlBuffer (EventType varchar(500),Parameters varchar(500),EventInfo varchar(5000)) --save block infor

OPEN cur_wait_kill
FETCH NEXT FROM cur_wait_kill into @spid,@status,@hostname,@hostprocess,@loginame,@program_name,@open_tran,@waittime,@cmd
while (@@fetch_status = 0)
begin
/*
If we dont want to save block infor,omit next 2 steps(delete and insert temp table),I tried my best to achieve to insert exec('dbcc inputbuffer (' + @spid + ')')
and other columns into table synchronously,but I can not find a method to do this,so I use two steps include a temp table to do it.
*/
delete from #SqlBuffer
insert #SqlBuffer exec('dbcc inputbuffer (' + @spid + ')')
insert into save_killed_session
(rq,
spid,
status,
hostname,
hostprocess,
loginame,
program_name,
open_tran,
waittime,
cmd,
EventType,
Parameters,
EventInfo
)
select
getdate(),
@spid,
@status,
@hostname,
@hostprocess,
@loginame,
@program_name,
@open_tran,
@waittime,
@cmd,
@EventType,
@Parameters,
@EventInfo
from
#SqlBuffer

set @kill = @kill + 'kill ' + cast(@SPID as varchar)+' '
FETCH NEXT FROM cur_wait_kill into @spid,@status,@hostname,@hostprocess,@loginame,@program_name,@open_tran,@waittime,@cmd
end
CLOSE cur_wait_kill
DEALLOCATE cur_wait_kill
drop table #SqlBuffer
exec(@kill)
/*If need send mail,Pls add it yourself*/
GO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242504/，如需轉載，請註明出處，否則將追究法律責任。