Killed a Process today.

So on a server I didn’t even know about they had an instance of SQL Server 2012 loaded which was used by a software package.  They had a problem with the original database, built a new one and came to me to delete the old one.  Due to a lack of information what I wanted to do was rename the database adding “_backup” to the end of it, and take it offline so that a new one could be built before removing the old one, just to be safe.  This is when I learned that taking a database offline could be a bit trickier than one would think.  First to rename it I used the standard:

ALTER DATABASE <DATABASE>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

EXEC SP_RENAMEDB <DATABASE>,<DATABASE_BACKUP>;

ALTER DATABASE <DATABASE_BACKUP>
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;

This was out of habit.  I then went to use the “Take Offline” task from the right-click menu on the database.  This opened up a new popup window showing the progress of the process which hung forever.  This probably could have been prevented by using a T-SQL statement like:
ALTER DATABASE <DATABASE_BACKUP>
SET OFFLINE WITH
ROLLBACK IMMEDIATE;

But this is not how I rolled today, so the offline process hung, and hung…  Which was lucky because it lead me to this blog which used a script to kill connections.  I wanted to be able to save this script as a procedure but you can’t use USE functions in the procedures so I had to adapt it a little which now lets me call it from MASTER and pass in the database I am concerned about.  Here’s the final script.

 

Leave a Reply

Your email address will not be published. Required fields are marked *