Home Dashboard Directory Help

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



Sign in
to vote
Type: Bug
ID: 813014
Opened: 1/5/2014 5:30:35 PM
Access Restriction: Public
User(s) can reproduce this bug


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 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.
Sign in to post a workaround.