Recently I came across an issue in SQL Server 2008 SP1 where I was trying to rebuild our DEV databases from script (we have Nant scripts that perform a continuous build process so we know that they work) but during the rebuild the process failed because one of the databases could not be dropped. At first I thought that there must be existing connections to the database but a quick review of the output of "sp_who2" showed no such thing.
Anyway, to shortcut the whole build process I figured I would just use SQL Server Management Studio to manually drop the database and enable the "force close existing connections" option. However, this too failed saying that the database was currently in use, even with the "force close" option set and even more bizarrely once again when I checked the output of "sp_who2", I could not see any connections to the particular database in question. Restarting the SQL Server service didn't help either.
A quick Google search on this error message produced quite a few hits (as you probably know if you found this page that way), a lot of which seemed to indicate that the way to resolve this is to alter the database into single user mode with the immediately rollback option set. Using this produced no joy for me either, which wasn't really surprising considering that there appeared to be no connections in the first place.
In the end, this is how I solved it… You need to get SQL Server to tell you who is blocking the drop command. Not rocket science is it, once you know the answer. ;-) So…
- First of all, using SQL Server Management Studio, open a query window on to the server and ensure that it is connected to the [master] database.
- Next, also using SQL Server Management Studio, right click on the database in question and select the option to drop the database and enable the "close existing connections" option. Click OK.
- While this is trying to drop the database, quickly switch to the open query window and run "sp_who2". Look through the result set for the row with a status of "SUSPENDED" and note the value in the "BlkBy" column.
- Also in the query window, execute the kill command to kill the spid with the value noted in the point above.
And there you go, the database will have been dropped as soon as you killed the SPID.
Wierd eh?
