Thursday 11 March 2010

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

Wednesday 3 March 2010

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.