Home Forums SQL Server 2005 Administering Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state RE: Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state

  • 2ndHelping (9/7/2012)


    alter database <mirrorDB> set partner FORCE_SERVICE_ALLOW_DATA_LOSS

    This allows the mirror DB to come online without any further attempt on the server's part to apply more log bytes from the principal.

    Thanks 2ndHelping. Its a nice idea but, unfortunately, not one that will work in this case.

    As promised I will post up the scenario. I have mirroring set up in high-performance mode.

    During a heavy processing load mirroring generates an error on the mirror server:

    "the remote mirroring partner for database 'ABRS_NABNSA', encountered error 9003, status 15, severity 20. Database mirroring has been suspended"

    The log on the mirror has "The log scan number (16524:1595833:2) passed to log scan in database '' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

    Attempting to resume the mirroring seems to have little effect, no errors are generated and the Database Mirroring Monitor shows 'Synchronizing' but with an impossibly slow restore rate.

    Given that, in all of this the transaction log had blown out because the processes on the principal were still running I decided the most sensible thing to do was to remove the mirror, shrink the transaction log and reinitialise.

    I ran ALTER DATABASE <DBName> SET PARTNER OFF on the principal, but when I went to run this command on the mirror I received the message "The command failed because the database mirror is busy. Reissue the command later.".

    Attempting to run a RESTORE DATABASE WITH RECOVERY command returns a message " exclusive access could not be obtained because the database is in use"

    At this stage the mirror database is at "Mirror, Disconnected/In Recovery".

    I restarted the SQL Server service on the mirror server and received the message "Database mirroring has been terminated for database"

    So I figured I'd just wait it out and see what happened.

    Some 3 hours, the mirror database had recovered and was online.

    So I concluded that there was some mammoth roll forward/back process that was going on in order to bring the (previously) mirrored database into a consistent state? Also I was unable to fathom why the "invalid logscan number" error was generated.

    However, I just discovered a cumulative update http://support.microsoft.com/kb/2403218/en-us?sd=rss&spid=2855 which looks rather like the ticket!