Sql Server 2016 Cannot drop database (it is really, really, really stuck)

Question

I have a database that was involved in a mirroring session and something crashed hard and the database is stuck 'in recovery' and will not allow me to drop it because it thinks the database is in use although I can't see what.

I run this and the affected database is not listed:

SELECT DB_NAME(dbid) as 'DBName'
     , loginame as 'Login'
     , COUNT(dbid) as 'Connections' 
FROM sys.sysprocesses 
WHERE dbid > 0 
GROUP BY dbid, loginame

I also run EXEC sp_who2 and the database is not listed either

I run

ALTER database careplans set offline with ROLLBACK IMMEDIATE; DROP database careplans;

and I get an error "ALTER DATABASE failed because a lock could not be placed on database 'careplans'. Try again later."

This brings back no results:

select * from sys.sysprocesses where dbid = DB_ID( 'careplans')

This gives me the 'lock could not be placed' error:

ALTER DATABASE careplans
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;

This gives me an "Exclusive access could not be obtained because the database is in use." error:

RESTORE DATABASE CarePlans WITH RECOVERY

Any of the following give me the 'lock could not be placed' error:

ALTER DATABASE careplans SET  SINGLE_USER WITH NO_WAIT 
ALTER DATABASE careplans SET EMERGENCY; 
DBCC CHECKDB (careplans, REPAIR_ALLOW_DATA_LOSS) 
ALTER DATABASE careplans SET online; 
ALTER DATABASE careplans SET  Multi_USER WITH NO_WAIT

I have no idea what else I can try...?

Answer 1

This issue is pretty tricky to reproduce so I can suggest to try two more solutions, but both require SQL Server to be restarted:

1. Start SQL Server in single user mode (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode) (just add SQL Server service startup parameter -m and restart the service). Then using SQL CMD connect to the SQL instance using local administrator account and try to drop database using DROP DATABASE statement. Perhaps this time you won't get an error related to exclusive access acquisition.

2. Another way (pretty primitive I know! But it might be effective either!) is to stop SQL service and delete (or change location) of database files. Then when you start SQL Server back this database should come up in 'recovery pending' status and you should be able to remove it.

Answer 2

Your check for sessions on a specific database is not enough. Said database can be in use without you seeing sessions with its database id.

There are situations where you might be issuing a statement on a database, but using resources against another:

• use of synonyms -> tables from another db
• execute a procedure on a db that uses a 4 part name table/view
• execute a proc that executes a proc that ..
• linked server, replication jobs etc

You might be better off with using sp_lock/sys.dm_tran_locks and finding out what's locking your resources.

So while hammering the service is alright at times, it's not always practical in production (the instance might serve multiple databases/customers).

A more resilient way is to build a script to:

• get sessions that lock resources on your db
• kill all those sessions
• set db single user
• drop/kill/burn with fire said db

In case you know which app/users/hosts have most access to it (via a service/app etc), disable that user, and also stop the SQL Agent to stop replication/maintenance jobs to keep retrying.

It looks like overkill to kill sessions and then setting db single user, but I've had similar cases where setting db offline didn't work until I managed to kill those sessions (many requests per sec coming from multiple services/apps/hosts).