Cannot reliably drop a database in T-SQL script - by Greg Low - Australia

Status : 


Sign in
to vote
ID 813014 Comments
Status Active Workarounds
Type Bug Repros 7
Opened 1/5/2014 5:30:35 PM
Access Restriction Public


Databases can only be dropped when not in use. BOL suggests setting the database to single user with rollback immediate prior to dropping the database. The problem with this, is that it is performed in the context of the master database and a race condition occurs. Another connection can occur between the alter to change to single user and the drop statement.
Sign in to post a comment.
Posted by Robert L Davis on 3/17/2016 at 5:39 AM
We need a T-SQL command equivalent to the KillDatabase method in SMO.
Posted by Nick_Craver on 1/12/2014 at 7:29 AM
We experience this issue as described and in another common similar scenario: bringing a database out of READONLY, since another connection steals that single user connection. In any scenario where you have many clients (in our case 11 high traffic web servers) combined with single-connection-allowed, multi-step changes, a connection occuring between them is VERY common. This immediately causes failure. The worst aspect is it's not a clean fail, you've gone from multi-user read-only to single-user readonly throwing errors on 10 servers and you're often left in single user mode with and some 1 web server has that connection. In a high traffic environment that's not a simple recovery either.

Please allow some way to combine steps in both dropping database and read-only/read-write changes - they're a huge pain point in an active environment currently.