DECLARE @SPID AS INT
DECLARE @COUNT AS INT=0
DECLARE curSPID CURSOR FOR
SELECT A.SPID --,DB_NAME(A.DBID) DB,A.login_time,SUBSTRING(SUSER_SNAME(sid),1,30) 登陆帐号,A.CMD,A.open_tran,A.last_batch,A.hostname,A.program_name,A.loginame,b.text SQL --,waittype 被锁进程等待类型
FROM master..sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE A.status='SLEEPING' AND A.DBID>=7 AND DATEDIFF(MINUTE,A.last_batch,GETDATE())>=30
ORDER BY A.last_batch
OPEN curSPID
FETCH NEXT FROM curSPID INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('KILL '+ @SPID)
SET @COUNT+=1
PRINT 'KILL '+ CAST(@SPID AS VARCHAR)
FETCH NEXT FROM curSPID INTO @SPID
END
PRINT 'KILL COUNT:'+ CAST(@COUNT AS VARCHAR)
CLOSE curSPID
DEALLOCATE curSPID
SQL Server KILL Sleeping进程
最新推荐文章于 2024-07-10 17:21:35 发布