当前位置：首页 > article >正文

sql server 自动kill 查询超过20分钟的语句

article 2024/11/13 18:19:01

起源于同事的烂sql 容易拖垮数据服务器，

周末没有人监控数据库，好几次导致主从数据库同步失败，不得不自动kill 烂sql

语句如下：

-- 声明变量来存储超过20分钟的查询的会话ID
DECLARE @kill_sessions TABLE (session_id INT);

DECLARE @sql NVARCHAR(MAX) = '';

-- 终止找到的会话
DECLARE @session_id INT;
-- 插入超过20分钟的查询的会话ID到表中
INSERT INTO @kill_sessions
SELECT session_id
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
WHERE
-- 过滤出执行时间超过20分钟的查询
DATEDIFF(SECOND, req.start_time, GETDATE()) > 1200
AND st.text NOT LIKE '%--%kill_sessions%--%' and req.commAND='select';-- 避免终止这个脚本自身的会话

   --select * from @kill_sessions
   -- SELECT TOP 1 @session_id = session_id FROM @kill_sessions;
   -- PRINT 'Killing session ID: ' + CAST(@session_id AS VARCHAR(10));

--DELETE FROM @kill_sessions WHERE session_id = @session_id;
--KILL @session_id;

WHILE EXISTS (SELECT 1 FROM @kill_sessions)
BEGIN
SELECT TOP 1 @session_id = session_id FROM @kill_sessions;
PRINT 'Killing session ID: ' + CAST(@session_id AS VARCHAR(10));
select @sql= @sql + 'KILL '+cast( @session_id as varchar(10))
EXEC sp_executesql @sql
DELETE FROM @kill_sessions WHERE session_id = @session_id;
END;

再在sql server 代理做定时任务，