Kill All Connections to an SQL Server Database

Have you ever tried to restore a database using SQL 2008 and the restore failed since the database is in use? In 2005 we used to open “Detach” database and click on the hyperlink of the existing connection which will open the activity monitor and show existing connections.

Currently in SQL 2008 clicking the hyperlink will only display a message informing you that the database is currently in use without redirecting you to the connections page, and you will have to go to the activity monitor, find connections related to your database and kill them one by one.

However, I found an interesting Stored Procedure that kills all database connections

By Henry Huey’s:

http://www.imiscommunity.com/sql_stored_procedure_to_kill_all_connections_to_a_database

Run the script that follows against the master db, then execute the procedure like this:

sp_KillSpidsByDBName MyDBName

CREATE PROCEDURE dbo.sp_KillSpidsByDBName     @dbname sysname = ''ASBEGIN

-- check the input database nameIF DATALENGTH(@dbname) = 0 OR LOWER(@dbname) = 'master' OR LOWER(@dbname) = 'msdb'    RETURN

DECLARE @sql VARCHAR(30) DECLARE @rowCtr INTDECLARE @killStmts TABLE (stmt VARCHAR(30))

-- find all the SPIDs for the requested db, and create KILL statements --   for each of them in the @killStmts table variableINSERT INTO @killStmts SELECT 'KILL ' + CONVERT (VARCHAR(25), spid)    FROM master..sysprocesses pr    INNER JOIN master..sysdatabases db    ON pr.dbid = db.dbid    WHERE db.name = @dbname

-- iterate through all the rows in @killStmts, executing each statementSELECT @rowCtr = COUNT(1) FROM @killStmtsWHILE (@rowCtr > 0)    BEGIN        SELECT TOP(1) @sql = stmt FROM @killStmts        EXEC (@sql)        DELETE @killStmts WHERE stmt = @sql        SELECT @rowCtr = COUNT(1) FROM @killStmts    END

END

GO

Follow this link:
Kill All Connections to an SQL Server Database