Terminate Connections to an SQL Database

Have you ever tried to restore a database to an SQL server with existing connections? Unless the connections are terminated, the database restore will fail with the error that you cannot obtain exclusive access. You can manually kill each connection, but with many users this can be time consuming. I’ve found the SQL server just doesn’t do a good job at this. This script does a really good job of killing connections to a specific database.

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @USRCOUNT varchar(3),
  @PRINTUSRCOUNT VARCHAR (255), 
  @KILLSTATE varchar(32),
  @spid smallint 

-- Query for id number of all connections
use master
set @strsql = "select hostname,program_name,spid from sysprocesses where database='Database Name' order by hostname"

create table #tokill
(
  hostname varchar(50),
  program_name varchar(50),
  spid varchar(5)
)

-- Insert into temp table
INSERT INTO #tokill EXEC(@strsql)

-- Comment out the query below to hide connections
select * from #tokill

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(*)) FROM #tokill)
SET @PRINTUSRCOUNT = 'Attempting to disconnect ' + @USRCOUNT + ' Connections' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 10 seconds
WAITFOR DELAY '00:00:10' 

-- Begin cursor 
DECLARE CUSRKILL SCROLL CURSOR FOR 
SELECT SPID FROM #tokill 

OPEN CUSRKILL

FETCH FIRST FROM CUSRKILL INTO @spid 
SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
PRINT @KILLSTATE 
EXEC(@KILLSTATE ) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM Cusrkill into @spid 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
    PRINT @KILLSTATE 
    EXECUTE( @KILLSTATE ) 

    FETCH NEXT FROM Cusrkill INTO @spid 

  END 
END 
CLOSE Cusrkill 
DEALLOCATE Cusrkill 

--End cursor
PRINT 'Finished Killing IMA SPIDs'
DROP TABLE #tokill