Kill all connections in a database

Every now and then I've come across situations where you need to drop all connections to a database, so I came up with a little proc to auto generate and execute KILL statements against all processes for a certain database.

My original code for this was titled sp_massmurder_db, and was as follows.

CREATE PROC [dbo].[sp_MassMurder_DB] @DBName varchar(100)  AS    SET NOCOUNT ON    DECLARE @Spid int, @SQL Varchar (1000)    DECLARE DESTROY CURSOR FOR  SELECT spid  FROM master.dbo.sysprocesses (NOLOCK)  WHERE dbid = DB_ID(@DBName)    OPEN DESTROY  FETCH NEXT FROM DESTROY INTO @spid  WHILE @@FETCH_STATUS = 0   BEGIN      SET @SQL = 'KILL ' + CONVERT(Varchar, @SPID)    EXEC (@SQL)    FETCH NEXT FROM DESTROY INTO @spid     END  CLOSE DESTROY  DEALLOCATE DESTROY

Recover a Suspect SQL 2005 database

One day it will happen - a database will drop off the face of the earth while someone is working on it, or will not appear after a restart, and you get the terrifying words ...

     Database cannot be opened.  It has been marked SUSPECT    

Step One - Don't Panic!! It's easy to do, and it is what most of the rest of your company will be doing, especially if it is a main production database.

Step Two - read step one again!

Now read the error log - there will probably be a clue in there as to why it has been marked SUSPECT, along with the helpful advice of restoring the database from a backup.