Cannot reliably drop a database in T-SQL script
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.
Upvotes: 55<=-=Jan 12 2014 7:29AM=-=>
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.<=-=Mar 17 2016 5:39AM=-=>
We need a T-SQL command equivalent to the KillDatabase method in SMO.