i'm facing an issue with a corrupted secondary database on SQL Server 2016. After a massive outage we were able to restore most of the databases immediately without issues. Two databases were in the "Suspended" state. One of these two could simply resume mirroring when told to do so.
This one last database is completely stuck in (Mirror, Disconnected / In Recovery) . By now I was able to deactivate mirroring on the primary and make it accessible again (no mirroring enabled). The secondary however is completely broken. I can't deactivate mirroring (GUI or T-SQL or dbatools)
ALTER DATABASE <dbname> SET PARTNER OFF
This claims to be successful. If i then proceed and try to drop the database it gives me the following error:
Msg 3743, Level 16, State 1, Line 3 The database 'dbname' is enabled for database mirroring. Database mirroring must be removed before you drop the database.
I also tried using dbatools-commands to repair the mirroring. I've deleted the endpoint before trying to delete the database but every action fails with the info that mirroring is still active for this database. I seem to not be able to remove it from mirroring. I tried simply re-enabling mirroring on the primary which results in saying:
Database is enabled for Database Mirroring, but has not yet synchronizes with its partner. Try the operation again later. (Microsoft SQL Server, Error 956)
I get error mails that the server is unable to redo logs
I then proceeded to check the file explorer and there is no mdf nor ldf file for this database?! I've rebooted the complete machine and sql service multiple times now and no, the file is not hidden.
I can't take it offline, detach it or overwrite it during restore. I also tried setting the database to emergency mode and running DBCC against it but I alwas get the same error message that the datbase is enabled for mirroring and therefore can't be edited.
Any ideas how to fix this?
