· 10 years ago · Sep 20, 2016, 03:14 AM
1--exec sp_who2
2
3-- kill a single db connection
4 USE master;
5 GO
6 ALTER DATABASE Leelanau_JMS
7 SET SINGLE_USER
8 WITH ROLLBACK IMMEDIATE ;
9 GO
10 ALTER DATABASE Leelanau_JMS
11 SET MULTI_USER;
12 GO
13
14
15-- all connections
16USE master
17 GO
18
19 if EXISTS (select * from master.dbo.sysobjects where NAME = 'proc_kill_dbconnections' AND TYPE = 'P' )
20 DROP PROCEDURE dbo.proc_kill_dbconnections
21 GO
22
23 CREATE PROCEDURE proc_kill_dbconnections
24 @dbname sysname
25 WITH encryption
26
27 AS
28
29 BEGIN
30 SET nocount ON
31
32 ------- prepare temporary tables
33 -------------------------------------------
34 CREATE TABLE # temp_processes_table
35 (
36 spid INT PRIMARY KEY ,
37 ecid INT ,
38 status sysname,
39 loginame sysname NULL ,
40 hostname sysname NULL ,
41 blk INT ,
42 dbname sysname NULL ,
43 cmd sysname NULL ,
44 request_id INT NULL ,
45 mod INT NOT NULL DEFAULT 0
46 );
47
48 -- get processes list
49 --------------------------
50 INSERT INTO # temp_processes_table (spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id) EXEC sp_who
51
52 DECLARE @ COUNT INT
53 DECLARE @process_id INT
54 DECLARE @cmd varchar(8000)
55
56 SELECT @ COUNT = COUNT ( * ) FROM # temp_processes_table WHERE mod = 0
57
58 while @ COUNT > 0
59 BEGIN
60 SET @process_id = NULL
61
62 SELECT top 1 @process_id = spid FROM # temp_processes_table WHERE mod = 0 AND dbname = @dbname
63
64 if @process_id IS NULL
65 break
66
67 print @process_id
68
69 SET @cmd = 'kill ' + CAST (@process_id AS varchar)
70
71 EXEC (@cmd)
72
73 UPDATE # temp_processes_table SET mod = 1 WHERE spid = @process_id
74
75 SELECT @ COUNT = COUNT ( * ) FROM # temp_processes_table WHERE mod = 0 AND dbname = @dbname
76 end
77
78 -- cleanup - drop temporary tables
79 ---------------------------------------
80 DROP TABLE # temp_processes_table
81 end
82 GO
83
84 -- example
85
86 -- exec dbo.proc_kill_dbconnections 'Northwind'