1 --查詢正在執行的sql并停止 2 IF (EXISTS (SELECT * FROM sys.objects WHERE name = 'PRO_QUERY_KILL_EXECUTING_PRO')) 3 DROP PROC [dbo].[PRO_QUERY_KILL_EXECUTING_PRO] 4 GO 5 6 CREATE PROC [dbo].[PRO_QUERY_KILL_EXECUTING_PRO] 7 AS 8 /*-------------------------------------------------------------------- 9 Purpose: Shows what individual SQL statements are currently executing. 10 ---------------------------------------------------------------------- 11 Parameters: None. 12 Revision History: 13 15/02/2017 Ian_Stirk@yahoo.com Initial version 14 Example Usage: 15 1. exec dbo.PRO_QUERY_KILL_EXECUTING_PRO 16 ---------------------------------------------------------------------*/ 17 BEGIN 18 -- Do not lock anything, and do not get held up by any locks. 19 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 20 21 22 -- What SQL Statements Are Currently Running? 23 24 25 --declare @querySql varchar(2000) = 'SELECT [Spid] = session_Id 26 --, ecid 27 --, [Database] = DB_NAME(sp.dbid) 28 --, [User] = nt_username 29 --, [Status] = er.status 30 --, [Wait] = wait_type 31 --, [Individual Query] = SUBSTRING (qt.text, 32 --er.statement_start_offset/2, 33 --(CASE WHEN er.statement_end_offset = -1 34 --THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 35 --ELSE er.statement_end_offset END - 36 --er.statement_start_offset)/2) 37 --,[Parent Query] = qt.text 38 --, Program = program_name 39 --, Hostname 40 --, nt_domain 41 --, start_time 42 --FROM sys.dm_exec_requests er 43 --INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid 44 --CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt 45 --WHERE session_Id > 50 -- Ignore system spids. 46 --AND session_Id NOT IN (@@SPID) -- Ignore this current statement. 47 --ORDER BY 1, 2' 48 49 declare icursor cursor for SELECT [Spid] = session_Id 50 FROM sys.dm_exec_requests er 51 INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid 52 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt 53 WHERE session_Id > 50 -- Ignore system spids. 54 AND session_Id NOT IN (@@SPID) -- Ignore this current statement. 55 56 open icursor; 57 declare @spid VARCHAR(10) 58 while 1=1 59 begin 60 fetch next from icursor into @spid 61 IF(@spid IS NOT NULL AND LEN(@spid) <> 0) 62 begin 63 exec ('kill ' + @spid) 64 end 65 IF @@FETCH_STATUS != 0 BREAK; 66 end 67 close icursor 68 deallocate icursor 69 70 END 71 GO 72 73 exec PRO_QUERY_KILL_EXECUTING_PRO View Code

參考：http://www.2cto.com/database/201307/227503.html

? ? ? ??http://blog.csdn.net/wsc449/article/details/7242750

轉載于:https://www.cnblogs.com/sunshineyy/p/6401885.html

總結

以上是生活随笔為你收集整理的SQL Server查询正在执行的存储过程并停止的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。