SQL Server查询正在执行的存储过程并停止 

 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

posted @ 2017-02-15 16:10  sunshineyy  阅读(5577)  评论(0)  编辑  收藏  举报