2

I have a SQL Server 2014 DB where the log file filled up the disk. Since it was a test db, we decided to just delete the whole database. We took it offline, but when we try to drop it, we get a message that it cannot be dropped since it is in use.

How can it be in use when it is offline? How can I get it dropped? (Preferable without taking it online.)

Improve this question
14
  • 1
    If you try this ALTER DATABASE yourDB SET OFFLINE WITH ROLLBACK IMMEDIATE; GO DROP DATABASE yourDB; GO what error message you get?
    – SqlWorldWide
    Jun 13, 2017 at 20:18
  • Kendra Little explains is pretty well here
    – John Eisbrener
    Jun 13, 2017 at 20:19
  • 2
    Make sure you issue the command from master, and close any other query windows / Object Explorer.
    – Aaron Bertrand
    Jun 13, 2017 at 20:27
  • 1
    Open new query window and issue your command drop database(don't use with rollback immediate), note the spid. Now execute in another window select * from sys.dm_os_waiting_tasks wt where session_id = your_noted_spid, and look at the column blocking_session_id
    – sepupic
    Jun 13, 2017 at 20:49
  • 1
    Doing so you'll find what sessions prevent you from dropping, are there system sessions or user session? Once found these sessions, exec DBCC INPUTBUFFER(spid) to find out what they executed last
    – sepupic
    Jun 13, 2017 at 20:52

2 Answers 2

Reset to default
2

The problem was that the person who set the database to OFFLINE also set it to SINGLE_USER, and his session was still open and "using" the database, even if it was OFFLINE. When he closed that session we were able to drop the database.

Thx to everyone in the comments for helping.

Improve this answer
1

The problem you saw was actually because another spid held a "SHARED_TRANSACTION_WORKSPACE" lock on the dbcat (database catalog). In your case, you could have seen that lock by running this SELECT DB_NAME(resource_database_id) AS DbcatName, * FROM sys.dm_tran_locks; or this EXEC sp_lock NULL;.

Here are some things that I've learned (still true in Sql2019):

  1. DROP DATABASE will fail when other spids hold a shared lock on the dbcat (FYI this happens when a spid is connected to, or otherwise using, the dbcat).
  2. DROP DATABASE won't delete the files if the dbcat is OFFLINE.
  3. DROP DATABASE won't delete the log file if the dbcat is in EMERGENCY mode.
  4. ALTER DATABASE will fail if the dbcat is RESTORING (and perhaps RECOVERING, RECOVERY PENDING, and SUSPECT; couldn't test).

From my experience, if a dbcat is in single-user mode at the time a spid takes it OFFLINE, its shared lock on that dbcat won't be released until it either

  • disconnects from the server
  • or it changes the dbcat back to multi-user.

So this is the pattern I follow now:

ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--do maintenance/backup stuff...
ALTER DATABASE xxx SET OFFLINE; --gives you that "Failed to restart the current database. The current database is switched to master." message
ALTER DATABASE xxx SET MULTI_USER; --but you still own that "single user" connection to it, due to that shared DB-level lock.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.