0121 31 45 374
Qoute Icon

Drop/Kill all connections to a SQL Database

Tim

Every now and again you need to get exclusivity to a database e.g. to do a database restore but someone is connected. You can manually find who it is using EXEC sp_who but that can result in having to manually kill the various connections.

This is a little script we've found handy in the past to drop all connections to a SQL Database. I hope it's of use to you.

?
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
USE master
 
DECLARE @dbid INT
SELECT
    @dbid = dbid
FROM
    sys.sysdatabases
WHERE
    name = '## Your Database Name Here ##'
 
IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)
  BEGIN
    PRINT '-------------------------------------------'
    PRINT 'CREATE WOULD FAIL -DROPPING ALL CONNECTIONS'
    PRINT '-------------------------------------------'
    PRINT 'These processes are blocking the restore from occurring'
 
    SELECT spid, hostname, loginame, status, last_batch
    FROM sys.sysprocesses WHERE dbid = @dbid
 
    --Kill any connections
    DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
    SELECT spid FROM master.dbo.sysprocesses WHERE dbid = @dbid
    DECLARE @SysProcId smallint
    OPEN SysProc
    FETCH NEXT FROM SysProc INTO @SysProcId
    DECLARE @KillStatement char(30)
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
        EXEC (@KillStatement)
        FETCH NEXT FROM SysProc INTO @SysProcId
    END
 
    WAITFOR DELAY '000:00:01'
  END
< Back to Blog

Liked this post? Got a suggestion? Leave a comment