如何杀死闲置超过4小时的SQL服务器中的用户连接 [英] How to kill user connections in SQL server which are idle for more than 4 hours
本文介绍了如何杀死闲置超过4小时的SQL服务器中的用户连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好
我写了下面的代码来杀死sql中的用户连接。
但是现在我需要添加以下内容。
1)sp_who2应至少返回500行。然后必须继续使用剩下的部分。
2)此过程的空闲时间应超过4小时。 />
请告诉我如何在现有的地方添加这些功能..
谢谢
我尝试了什么:
CREATE TABLE #temp(
spid int ,
status varchar ( 256 ),
loginname varchar ( 256 ),
hostname varchar (< span class =code-digit> 256 ),
blkby varchar ( 256 ),
dbname varchar ( 256 ),
命令 varchar ( 256 ),
cputime int ,
diskio int ,
lastbatch varchar ( 256 ),
programName varchar ( 256 ),
spid2 int ,
requestid int )
< span class =code-keyword> INSERT INTO #temp
EXECUTE sp_who2
选择 * 来自 #temp
DECLARE kill_spids CURSOR FOR
- 在此处添加WHERE子句以指示whi ch杀死进程。
SELECT spid
FROM #temp
DECLARE @ spid SMALLINT
OPEN kill_spids
FETCH NEXT FROM kill_spids INTO @ spid
WHILE @@ FETCH_STATUS = 0
BEGIN
DECLARE @ dynamicsql NVARCHAR ( 4000 )
SET @ dynamicsql = ' KILL' + CAST( @ spid AS CHAR )
PRINT @ dynamicsql
- 当您确定你知道你在做什么,取消评论这一行
- EXECUTE sp_executesql @ dynamicsql
FETCH NEXT FROM kill_spids INTO @ spid
END
CLOSE kill_spids
DEALLOCATE kill_spids
DROP 表 #t使用 sysprocesses系统表 [ ^ ]更容易使用。
DECLARE @sql VARCHAR (MAX)= ' '
;
WITH cte AS
(
SELECT spid
FROM sys.sysprocesses
WHERE spid> 50
AND DATEDIFF(HOUR,last_batch,GETDATE())> = 4
)
SELECT @sql = @sql + ' KILL ' + CAST(spid AS VARCHAR ( 10 ))+ ' ;' + CHAR ( 13 )
FROM cte
- EXEC sp_executesql @sql
PRINT @sql
这不包括要求#1,但这只是一个额外的IF。
Hi all
I wrote the below code to kill the user connections in sql.
But now in this i need to add the below things.
1) sp_who2 should return at least 500 rows. then have to go-ahead with the rest of the part.
2) the idle time for the process should be more than 4 hours.
Please let me know how to add these functions in the existing..
Thank you
What I have tried:
CREATE TABLE #temp (
spid int,
status varchar(256),
loginname varchar(256),
hostname varchar(256),
blkby varchar(256),
dbname varchar(256),
command varchar(256),
cputime int,
diskio int,
lastbatch varchar(256),
programName varchar(256),
spid2 int,
requestid int)
INSERT INTO #temp
EXECUTE sp_who2
select * from #temp
DECLARE kill_spids CURSOR FOR
--Add a WHERE clause here to indicate which processes to kill.
SELECT spid
FROM #temp
DECLARE @spid SMALLINT
OPEN kill_spids
FETCH NEXT FROM kill_spids INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @dynamicsql NVARCHAR(4000)
SET @dynamicsql = 'KILL '+CAST(@spid AS CHAR)
PRINT @dynamicsql
--When you are sure you know what you're doing, un-comment this line
--EXECUTE sp_executesql @dynamicsql
FETCH NEXT FROM kill_spids INTO @spid
END
CLOSE kill_spids
DEALLOCATE kill_spids
DROP TABLE #temp
解决方案 you can achieve the same using sysprocesses system table[^] which is even easier to use.
DECLARE @sql VARCHAR(MAX) = ''
;
WITH cte AS
(
SELECT spid
FROM sys.sysprocesses
WHERE spid > 50
AND DATEDIFF(HOUR, last_batch, GETDATE()) >= 4
)
SELECT @sql = @sql + 'KILL '+CAST(spid AS VARCHAR(10)) + ';' + CHAR(13)
FROM cte
--EXEC sp_executesql @sql
PRINT @sql
This doesn't include the requirement #1 but that's just one additional IF.
这篇关于如何杀死闲置超过4小时的SQL服务器中的用户连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文