Kill Connections to Specific Database

,

Ever needed to drop all connections to a database? Here is an easy way to do it.

/*Kill Connections to Specific Database
CREATED BY: Doug Smith
CREATE DATE: 11/08/2006
PURPOSE: To give DBA an easier way to drop connections to a specific database
NOTE----Change value of parameter(@dbname) to the database name that need connections dropped.
*/

DECLARE @dbname varchar(30)

SET @dbname = 'database'

CREATE TABLE #ConnsToKill
(spid int
,login varchar(50)
,dbname varchar(30)
,killed bit default 0)


INSERT INTO #ConnsToKill
SELECT cast(sps.spid as varchar(3))
, rtrim(sps.hostname)
,rtrim(sdb.name)
,0
FROM master..sysprocesses sps
JOIN master..sysdatabases sdb ON sps.dbid = sdb.dbid
where sdb.name = @dbname

DECLARE @spid varchar(3)

WHILE exists (SELECT 'x' FROM #ConnsToKill WHERE killed = 0)
	BEGIN
		SELECT @spid = 
		(SELECT TOP 1 spid 
		FROM #ConnsToKill
		WHERE killed = 0)


			EXEC('Kill '+@spid+'')
				

		UPDATE #ConnsToKill 
		SET killed = 1
		where spid = @spid
	END
DROP TABLE #ConnsToKill 
GO

Rate

5 (3)

Share

Share

Rate

5 (3)