Blog

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

Share this post



Leave a comment


Previous Post

Next Post


\