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):
- 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).
- DROP DATABASE won't delete the files if the dbcat is OFFLINE.
- DROP DATABASE won't delete the log file if the dbcat is in EMERGENCY mode.
- 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.
ALTER DATABASE yourDB SET OFFLINE WITH ROLLBACK IMMEDIATE; GO DROP DATABASE yourDB; GOwhat error message you get?