Drop/Kill all connections to a SQL Database

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.

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