Archive

Posts Tagged ‘SQL’

Kill all database connections

June 7th, 2009 No comments

Just came across this script – very useful

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

--------------------------------------------------------------------------------------
/*** CREATED BY AJITH DHARWAR                                                     ***/
/*** This procedure will accept a DB name and drop all connections to the DB.    ***/
--------------------------------------------------------------------------------------
CREATE PROCEDURE usp_killDBConnections
    @DBName VARCHAR(50),
    @withmsg BIT = 1
AS 
    SET NOCOUNT ON
    DECLARE @spidstr VARCHAR(8000)
    DECLARE @ConnKilled SMALLINT
    SET @ConnKilled = 0
    SET @spidstr = ''
 
    IF DB_ID(@DBName) > 4 
        BEGIN
            PRINT 'Connections to system databases cannot be killed'
            RETURN
        END
 
    SELECT  @spidstr = COALESCE(@spidstr, ',') + 'kill ' + CONVERT(VARCHAR, spid) + '; '
    FROM    master..sysprocesses
    WHERE   dbid = DB_ID(@DBName)
 
    IF LEN(@spidstr) > 0 
        BEGIN
            EXEC ( @spidstr
                )
 
            SELECT  @ConnKilled = COUNT(1)
            FROM    master..sysprocesses
            WHERE   dbid = DB_ID(@DBName) 
 
        END
 
    IF @withmsg = 1 
        PRINT CONVERT(VARCHAR(10), @ConnKilled) + ' Connection(s) killed for DB ' + @DBName
GO

You can then use it like this

1

EXEC sp_msforeachdb 'EXEC usp_killDBConnections @DBName=''?'''

Or like this

1

EXEC usp_killDBConnections @DBName='MyDatabase'
Categories: Programming, SQL Tags: ,