sqlserver 删除造成锁表的sleeping进程脚本

create  PROCEDURE dbo.Kill__Sleeping_Processes
AS
BEGIN
declare @v_sid int
declare @sql varchar(50)
declare cur_kill_sleep cursor for 
WITH sess AS
(
    SELECT
        blocking_session_id,        
        es.session_id,
        er.status,
        individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1,  ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))  * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1)
    FROM
        sys.dm_exec_requests er
        INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE
        es.session_id > 50
        AND es.session_Id NOT IN (@@SPID)
              AND qt.text <> 'sp_server_diagnostics'
)
SELECT
    session_id
FROM
    sess WHERE individual_query NOT LIKE 'waitfor%' and status='sleeping'
UNION ALL 
  select  es.session_id
FROM
    sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
WHERE  status='sleeping'  and
    ec.most_recent_session_id IN
    (
        SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT  IN(SELECT DISTINCT session_id FROM sess)
    ) AND  qt.text NOT in ('waitfor delay @waittime    ','sp_server_diagnostics')  

open cur_kill_sleep
fetch next from cur_kill_sleep into @v_sid
while @@fetch_status=0
begin
SET @sql = 'KILL ' + RTRIM(@v_sid)
EXEC ( @sql )
print '删除'+CONVERT(varchar(10),@v_sid)
fetch next from cur_kill_sleep into @v_sid
end
close cur_kill_sleep
deallocate cur_kill_sleep
END