Kill database connections in SQL Server
Sometimes you may want to get rid of all the open connections to a database for many reasons. One of the reasons could be delete the database or detach the database in order to move it to another server.
But you will not be able to do that if there are connections open with this database so you can use this procedure to kill all the open connections within the database.
CREATE PROCEDURE [dbo].[killDataBaseConnections] @DatabaseName VARCHAR(50), @WithMessage BIT=1
AS
BEGIN
SET NOCOUNT ON
DECLARE @spidstr VARCHAR(8000)
DECLARE @ConnectionKilled SMALLINT
SET @ConnectionKilled=0
SET @spidstr = ''
IF DB_ID(@DatabaseName) < 4
BEGIN
PRINT 'No can do...'
RETURN
END
SELECT @spidstr = COALESCE(@spidstr,',' ) + 'KILL ' + CONVERT(VARCHAR, spid) + '; '
FROM [master]..sysprocesses
WHERE dbid = DB_ID(@DatabaseName) IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr) SELECT @ConnectionKilled = COUNT(1) FROM [master]..sysprocesses
WHERE DBID = DB_ID(@DatabaseName)
END
IF @WithMessage = 1
PRINT CONVERT(VARCHAR(10), @ConnectionKilled) + ' Connection(s) killed for DB ' + @DatabaseName
END
