Can a offline database be in use?

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

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.

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.