by Jim
Nov 17, 2007 8:55 AM
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