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
Comment (1)
Hey I’m for the primary time here. I found this board and I in finding It truly helpful & it helped me out much. I’m hoping to provide something again and help others such as you aided me.