0

I've removed a db from a synchronous availability group.The db shows "synchronizing/suspect" as it should on the secondary. I'm trying to remove this database cannot. I have tried:

DROP DATABASE [db1];

but get "Cannot drop database "db1" because it is currently in use.". even though their are no sessions using this database.

ALTER DATABASE [db1] SET OFFLINE;

but get "ALTER DATABASE failed because a lock could not be placed on database 'db1'. Try again later."

ALTER DATABASE [db1]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

but get "ALTER DATABASE failed because a lock could not be placed on database 'db1'. Try again later."

ALTER DATABASE [db1] SET EMERGENCY;

ALTER DATABASE failed because a lock could not be placed on database 'db1'. Try again later.

This is a 24/7 production db and restarting is not a good option.

Any advice?

Improve this question

1 Answer 1

Reset to default
0

Does this StackOverflow answer help you? Summarized from the answer:

  1. Run EXEC sp_who2.
  2. Look for any open connections to the database in question.
  3. Run KILL SPID where the SPID is of the process for that connection.

You might also find Adam Machanic's sp_whoisactive helpful for locating the connection's process with additional information.

Improve this answer
5
  • No. I can see all the locks on this secondary database and all the spids but I cant kill these spids are they are all system spids. I did add certificates and encrypted the primary server db before trying to remove the secondary db.
    – SaltyTiger
    Jun 10, 2021 at 11:13
  • @SaltyTiger What happens when you try to kill one of the lock SPIDs?
    – J.D.
    Jun 13, 2021 at 13:05
  • I cant kill them, they're all system spids.
    – SaltyTiger
    Jun 14, 2021 at 8:27
  • @SaltyTiger Right, but what happens when you try to?...do you get an error?...if so, which error?...some context would be helpful. I believe you should be able to, at least with an account that has sysadmin server privileges. I've manually killed Replication system processes when they were causing locking issues, I would figure Availability Groups shouldn't be much different in that regard.
    – J.D.
    Jun 14, 2021 at 11:07
  • I get a "db in use" when I try drop the db on the replica. these a the spids and none of them can be killed as I get "Only user processes can be killed.". spid dbid ObjId IndId Type Resource Mode 115 22 0 0 DB U GRANT 385 22 0 0 DB [STARTUP] X GRANT 404 22 0 0 DB [HADR_STATE_MANAGEMENT] U GRANT
    – SaltyTiger
    Jun 15, 2021 at 8:11

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.