Kill all connection to a Microsoft SQL Server Database


 

Hi!

Thought that I should share this script that has helped me numerous times when I have had a need to kill all connection to a Microsoft SQL Server Database.

DECLARE @Database nvarchar(50)
SET @Database = 'Database_name'


DECLARE @Query varchar(max)
SET @Query = ''

SELECT @Query = @Query + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@Database) AND SPId <> @@SPId

Exec(@Query)

select dbid from MASTER..sysprocesses where dbid in (SELECT DBID from MASTER..sysdatabases where name = @Database)

Hope it helps!

//Karl-Henrik.