4

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

1
  • 4
    If you can restart the SQL Server service, rename the data/log files to something else. That way the database won't start up with the service, and nothing else will be using it.
    – Brent Ozar
    May 31, 2017 at 15:50

2 Answers 2

10

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.

3
  • 1
    Confirmed that #2 works. Stop SQL, delete (or move or rename) files, start SQL, DROP DATABASE.
    – BradC
    May 31, 2017 at 16:07
  • Starting in single user mode worked, it allowed me to remove mirroring and then drop the database. Deleting the database files did NOT work though interestingly...
    – user197505
    Jun 1, 2017 at 17:02
  • This is interesting! I'd bet some money 2nd option would work in your case. Anyway I am glad to hear you eventually solved the issue!
    – GrzegorzO
    Jun 2, 2017 at 9:57
1

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

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.