· Mirror database is suspect (hardware failure causing corruption), SQL Server can’t apply the redo logs on the mirror
· With the intent to recover DB and reestablish mirroring, ALTER DATABASE … SET PARTNER OFF is issued on the principal
o DB Mirroring breaks DBM session from the principal point of view, and then sends the message to the mirror
o Mirror tries to break the DBM session; however, SQL Server attempts to apply all the remaining log in the redo queue before it breaks the DBM session. This is by design. The goal is to apply all the logs it has received because the user may be attempting to break the mirror so as to read the data on the mirror database. It makes sense to apply as much redo log records as possible
· DBM session is not broken from the mirror point of view
· All subsequent actions (drop database, restore database) are blocked, because SQL Server still views this as a mirror database
Workaround:
Shutdown the instance and remove the mdf, ndf and ldf for the suspect database, and start the instance. You should be able to restore the instance, and recreate the mirror.
Is this work for sure
ReplyDeleteOne more doubt so when we shutdown the instance will other mirroring database gets disturbed?
As a DBA, I always suggested maintaining the proper backup of the database. It plays an important role in a critical situation. DBCC CHECKDB Repair_Allow_Data_Loss may or may not be the reason for data loss (clears from its name).
ReplyDeleteIn this case, I will suggest SQL recovery software. Watch this video: https://www.youtube.com/watch?v=IWWkyrPEaRA&t=1s