3

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.)

GHauan
  • 603
  • 7
  • 23
  • 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 '17 at 20:18
  • Kendra Little explains is pretty well here – John Eisbrener Jun 13 '17 at 20:19
  • @SqlWorldWide The database is already OFFLINE. When I run the DROP DATABASE command, I get the following: "Msg 3702, Level 16, State 4, Line 6 Cannot drop database "MY_DATABASE" because it is currently in use." And this seems weird to me on an offline database. Can it have to do with it being set to singleuser before offline? – GHauan Jun 13 '17 at 20:23
  • Can you verify that it is, in fact, offline? Try the following: SELECT name, user_Access_Desc from sys.databases. Does it say MULTI_USER or OFFLINE? – Kris Gruttemeyer Jun 13 '17 at 20:24
  • @KrisGruttemeyer The user_Access_Desc = SINGLE_USER, but the state_desc = OFFLINE. – GHauan Jun 13 '17 at 20:27
  • 2
    Make sure you issue the command from master, and close any other query windows / Object Explorer. – Aaron Bertrand Jun 13 '17 at 20:27
  • @AaronBertrand Did all that, but same result. However I cannot be sure that other users is not in Object explorer (it is late at night here, ppl are home). But when it is OFFLINE they should not have any connections anyway? Is it possible to make it MULTI_USER without taking it online? – GHauan Jun 13 '17 at 20:34
  • 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 '17 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 '17 at 20:52
  • @sepupic Thanks that was smart. It was the session a colleague of mine used when the DB was set offline. Is it safe to kill that session and then try dropping the database again, or is it best to wait until he is back at work tomorrow and do the dropping from that session? Any input on that? – GHauan Jun 13 '17 at 20:57
  • Try to kill it but it's strange, I'd rather beleive your db is still online and your colleague did not put it offline, try one more time to exec select state_desc from sys.databases where name = 'your_db' – sepupic Jun 13 '17 at 21:05
  • Sorry, I missed it, it was in single user but offline. Ok, thank you for interesting question, I'll try to reproduce it tomorrow – sepupic Jun 13 '17 at 21:07
  • It was too interesting so I tried it today. Killing that session change nothing: the database remains in single user, so the best way is to ask your colleague to put it in multi user, this should be done from the session that put it in sigle user – sepupic Jun 13 '17 at 21:16
  • @sepupic We just got it dropped. My colleague had closed that session this morning before I could get him to drop the database from there, but he was able to drop the database from the object explorer non the less. Thx to every one for good suggestions and help. – GHauan Jun 14 '17 at 07:16

2 Answers2

3

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.

GHauan
  • 603
  • 7
  • 23
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.
Granger
  • 111
  • 3