Tuesday, December 01, 2009 by Administrator
Quite often you need to kill all SQL Server connections to a
database...perhaps for performing a backup or restore.
Here is a snippet of SQL to kill connections to a given
database:
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YourDatabaseName'
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
--SELECT @SQL
EXEC(@SQL)