Thursday, July 3, 2008

Kill Database Connections

In some cases you want to delete or detach database in order to move it to another place.
But you can't do it if there are connections open with this database so you can use this procedure to kill all the connections with 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


Note: Don't do this if the database on a production server.
Source: http://www.nilebits.com/blog/2007/05/kill-database-connections-in-sql-server/

Hire Me

Follow me on Facebook

Follow me

Do you find this Blog helpful?

Follow by Email

About Me

My Photo
Expert Senior Software Developer

Microsoft Business Card