Sunday, June 19, 2016

Msg 1422, The mirror server instance is not caught up to the recent changes to database....

I ran into this error a couple weeks ago when I was applying a service pack to a couple of mirrored SQL Servers. I had just patched the Mirror and resumed the mirroring session, but when I tried to perform a manual failover of the Mirror to the Principal, I received this message:








We'll see this if we try to failover the mirroring session when the databases are not in a SYNCHRONIZED state.  Makes sense.. if you take a look at this post, you'll see the step before the manual failover was to resume the Mirror session.  It just wasn't completely synchronized yet.

To confirm how far behind the mirror was, I ran this query on both the Principal and the Mirror.  

     SELECT
         DB_NAME(database_id) [Database],
         mirroring_failover_lsn
     FROM
         sys.database_mirroring

The 'mirroring_failover_lsn' is the log sequence number that the Mirror partners use as the point of reconciliation. I waited a couple minutes and tried again.  The failover completed successfully because the LSN was now the same on both the Principal and Mirror instance.



















See this for more detail on sys.database_mirroring and the LSN:

And this for instructions on performing the manual failover via tSQL:
     https://msdn.microsoft.com/en-us/library/ms179481.aspx

No comments:

Post a Comment