Kill All

To continue my code dump, here is a little SQL stored procedure that comes in handy when debugging or testing requires constantly restoring a database. Inevitably there are various processes with connections open, and the restore doesn't work until each is found and killed.

This procedure simply loops through the open connections for a given database and kills each one.

CREATE PROCEDURE sp_KillAll
@DbName VARCHAR(100)
AS
/***********************************************************************
Kill all the spid's attached to a particular database. Very useful when 
continually restoring backups for testing.

Create this in the master database, and call it from anywhere; but you
can't kill your own process!
***********************************************************************/

IF db_id(@DbName) = NULL 
BEGIN
    PRINT 'DataBase dose not Exist'
END
ELSE
BEGIN
    DECLARE @spId VARCHAR(30)
        
    DECLARE TmpCursor CURSOR FOR
    SELECT  'Kill ' + CONVERT(VARCHAR, spid)  AS spId
      FROM master..SysProcesses
      WHERE db_Name(dbID) = @DbName
      AND spId <> @@SpId
      AND dbID <> 0
      
    OPEN TmpCursor
        
    FETCH NEXT FROM TmpCursor INTO @spId 
        
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC (@spId)
     
        FETCH NEXT FROM TmpCursor INTO @spId 
    END

    CLOSE TmpCursor
    DEALLOCATE TmpCursor
END 
GO