THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Reliably Dropping a Database in a T-SQL Script is Too Hard

This blog has moved! You can find this content at the following new location:

Published Monday, January 6, 2014 12:31 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AllenMWhite said:

In SMO there's a Server object method called KillAllProcesses('AdventureWorks'), where you specify the database name inside the parentheses.  If you capture the T-SQL executed when that method is called it returns the following query:

SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(N'AdventureWorks')

If any results are returned - say that Session ID 51 is using the database - SMO executes a Kill statement for each session returned.

Perhaps you could implement a similar set of logic in your script.

January 5, 2014 9:55 PM

Greg Low said:

Hi Allen,

Yes, one option is to hunt down and kill all spids that are connected to the database but even that has the potential for a race condition. One could connect as soon as you kill another.

There just should be an atomic way to say "make this database go away".

January 6, 2014 2:43 AM

Stephen Morris, PFE said:

If it really is the IntelliSense which is the culprit one solution would be to swap to SQLCMD mode - as Intellisense is turned off then.

January 6, 2014 3:18 AM

Calin OPREA said:

Hello there.

I implemented the DROP DATABASE WITH ROLLBACK IMMEDIATE a while ago, only to see that this particular condition is not gone. It's been happening for me for the past 3+ months; the solution that worked for me was to create two different steps in a job, one to put the database in single user, and the other one to actually drop the database. Both steps are using ROLLBACK IMMEDIATE.

January 6, 2014 5:03 AM

Greg Low said:

Hi Colin,

The problem is that DROP DATABASE doesn't have a rollback option, and having it in two separate statements doesn't work reliably.

January 6, 2014 5:49 AM

Greg Low said:

Hi Stephen, true but not all tools support SQLCMD mode. There should be a way to do this reliably in a T-SQL script.

January 6, 2014 5:50 AM

Tony said:

If I recall correctly, you can do the alter to single user in the context of the db youre altering, then switch to master for the drop, and your session retains the single user lock.

January 6, 2014 5:57 AM

Greg Low said:

Hi Tony,

No, note that the database might not exist when the script is first run. Also, as soon as you change to another DB context, another session could connect before your drop executes.

It simply needs to be an atomic operation, not two separate operations.

January 6, 2014 6:08 AM

Tony said:

I agree it needs to be an atomic operation, but currently the approach i describe works, as the lock is held by your session after the zswitch to master. This script does what you describe, and shows the lock still held:

USE master




USE Test


USE master

SELECT DB_NAME(resource_database_id), * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID



January 6, 2014 8:38 AM

Andy Warren said:

Greg, I like the idea of rollback immediate - simple and elegant.

January 6, 2014 10:43 AM

Anonymous said:

Would emergency mode work? I'm not sure what impact dropping a database in that mode has, but at least no one (except an admin -- you, presumably) would be able to connect...

January 6, 2014 3:00 PM

Bill said:

Strictly speaking (and annoying as the behavior is), I am afraid this connect request is not a bug, in that I can find no Microsoft promise that DROP DATABASE _must_ drop a database (they actually suggest the opposite can happen). As such, this connect request is more akin to a Design Change Request (DCR). For many development shops, DCRs are assigned a lower priority than bugs. DCRs invariably need a solid business justification (in order to get them considered in the current release, or even for the next release).

For DCRs and bugs, it is important to explain why workarounds are not viable. To determine whether a workaround is viable, I am afraid one must first know the cause of a problem. Without knowing a cause, it is possible (even likely) for many of the solutions (or suggested workarounds) to be worthless. I am afraid this connect request does not identify a cause (e.g. the title mentions a "T-SQL script" that has not been attached, and the text does not mention SSMS), and I am afraid it only partially identifies the effect (e.g. it does not explicitly identify the error or errors being raised by the T-SQL script).

A cause could be SSMS' Intellisense, AUTO_UPDATE_STATISTICS_ASYNC, or something else. A timely trace (keep in mind that ASYNC stats could have kicked off minutes to hours before SINGLE_USER was issued) can help uncover a cause. Or some TRY CATCH error handling might also prove useful. Without a cause identified, I have to assume the problem Microsoft will face (when reading this request) is: What code needs to be redesigned? For any large RDBMS product, I have to assume the team that handle connectivity is different from the team that handles security, is different from the team that handles DDL, and is different from the team that handles system metadata. And yet for such a problem, a solution is likely to involve all of those teams.

For an Intellisense cause, I believe Microsoft's intent was to design SSMS for DBA use (i.e. SSMS was not designed for use by users). So if the cause is rooted in Intellisense, Microsoft might suggest SQLCMD to be used by DBAs (instead of SSMS) as a workaround. As an added bonus, SQLCMD can be automated. But Microsoft will still need to consider: Is the design needing to be changed within SSMS or within the engine (note that the engine is designed to serve whatever its clients, such as SSMS, demand)?

Or, if the cause of the failure is rooted in AUTO_UPDATE_STATISTICS_ASYNC, the way to address this cause has already been documented in BOL (, under the SINGLE_USER section). Note that it would be useless to suggest SQLCMD as a workaround for this cause. Note that this cause is by design (hence why how to address it has already been documented in BOL).

And I am imagining that RESTRICTED_USER is potentially another workaround (for an unknown cause).

Because of the above, I am afraid I see a connect request that needs more work (such as scripted repro steps, the complete error or errors SSMS is raising). Also was is needed is the expected outcome, which is above (but not in the request) "What is really needed to get around this race condition is:


By stating the expected outcome (attaching the script, and mention SSMS) in the connect request, I have to assume Microsoft will be able to more precisely offer a solution or workaround,

Without such details I have to assume Microsoft will be wondering where this request should be routed. Even if routed correctly, I assume developers without a business justification are going to have a hard time justifying the work that will be needed to address this connect request :).

Instead of simply voting, I think it would more useful for voters to explain (as a Comment under the connect request) how their inability to reliably drop a database impacts or has impacted their business.

I am not claiming current behavior is a _good_ design :). I am not claiming the connect request is unworthy of a solution (or workaround). What I am claiming is that the request is likely to go nowhere (no matter how many vote for it), because I see insufficient data to compute :). I am claiming Microsoft has a top-heavy bureaucracy whose wheels need to be heavily greased (far more than you and I imagine :).

While handing a need to Microsoft upon a silver platter might seem rather onerous, I assume it will result in a more pragmatically useful Microsoft response:).

January 6, 2014 3:00 PM

Greg Low said:

Hi Adam,

EMERGENCY still allows multiple connections by sysadmin members.

January 6, 2014 6:04 PM

Greg Low said:

Hi Bill,

I see it as either an engine bug or (more likely) a documentation bug. If the doco says that you can drop a database this way and it doesn't always work, either the engine or the doco need changing.

January 6, 2014 6:05 PM

Greg Low said:

For Tony, I'm not getting the same result. If I go up to a system without a Test database and execute the code you have above:

USE master




 USE Test


 USE master

 SELECT DB_NAME(resource_database_id), * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID



What I get is:

Msg 911, Level 16, State 1, Line 4

Database 'Test' does not exist. Make sure that the name is entered correctly.

It seems to check that the database that's referenced by the USE actually exists before executing the batch.

January 7, 2014 1:45 AM

Tony said:


USE master





USE Test


USE master

SELECT DB_NAME(resource_database_id), * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID




January 7, 2014 7:44 AM

Greg Low said:

Hi Tony,

I like the idea of using dynamic SQL here and think that might work much more reliably. Even though it shows the resource being held, I suspect there still would be a chance of a race condition between when the database context changes back to master and the drop occurs but it's obviously very unlikely to fail.

I just wish there was a statement that did it cleanly. You should be able to just say "drop that database and disconnect anyone that was connected to it".

January 7, 2014 7:40 PM

Jon Morisi said:

Have you tried with NO_WAIT?


I wonder if the quicker alter database will yield you more reliable dropping.

January 8, 2014 10:55 AM

Greg Low said:

Hi Jon,

NO_WAIT doesn't work faster. It just fails if it can't get the required locks.

January 8, 2014 4:46 PM

Dave Ballantyne said:

Its not just DROPing a database that is affected, ive had similar issues rolling back databases to snapshot.

IMO what is need is


Or in english ,  set to single user and make that user me.

January 9, 2014 8:10 AM

Nick Craver said:

We have the same issue here at Stack Overflow, though more often with read-only/read-write changes.  The scenario isn't SSMS taking connections, but 11 high traffic web servers fighting for connections.  These operations don't have a small chance of failure - they will almost certainly fail.  Being stuck in single-user mode when the same 11 servers fighting for it is a bad place to be, so the fail case is pretty harsh.

I would LOVE an atomic operation for any operation that requires SINGLE_USER mode, using it online without other mechanisms is nearly impossible for us - and it has such a slim chance of working, it's not worth risking the fail.  Currently we have application level switches to disable databases and connections from the app tier - it's the only way to get things done.

January 12, 2014 10:40 AM

Henrik Staun Poulsen said:

Would it be possible to do a re-try, if the drop fails?

Something like:

create procedure DropTestDB as



   begin try


     DROP DATABASE hsptest

   End try

   Begin Catch

     exec DropTestDB

   End Catch




exec DropTestDB

January 17, 2014 9:39 AM

jeff_yao said:

I just created a CLR SP to drop the db (except for master database) as I put it here

January 19, 2014 8:25 PM

Greg Low said:

Hi Jeff,

That's cool but many people don't have CLR integration enabled. And you shouldn't need to drop out to CLR code just to drop a database reliably. So far, I think the dynamic SQL option that Tony suggested is the best option for straightforward T-SQL code. It's a pity to need to jump into dynamic code though.

January 19, 2014 8:34 PM

jeff_yao said:

Hi Greg,

Actually Tony's dynamic sql option is almost the same as my t-sql method mentioned in my, however, from a PURE theory perspective, as soon as "use master" is executed, another session can grab the target db, so from theory perspective, the target db should be switched to offline first and then drop it and then delete each data/log file used by this target db. That's why I think only CLR SP can accomplish this task easily.

But you are absolutely right, not every environment allow CLR enabled. :-)

January 20, 2014 1:07 PM

Justin Dearing said:

Unless your connect bug is implemented, the best way to do this is to write a CLR proc that will look in sys.database_files, put the database offline, drop it, and delete the files. You would need "external_access" permission and the proc would have to run with the same credentials as the sql service.

Yes its annoying to write all that C#, but I can't think of a better way.

June 24, 2014 1:48 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement