locked
any system table or system view to find out if the mirror server has caught up to the recent changes during failover RRS feed

  • Question

  • any system table or system view to find out if the mirror server has caught up to the recent changes during failover
    Monday, June 14, 2010 5:51 PM

All replies

  • Hello Kiran,

    1)Check the mirror monitor.

    Right click principal or mirror database -> Task -> Launch Database mirror monitor. Now register your principal or mirror server here.

    You could see the current status like UnSent log etc.

    2) Another way will be to see the perfmon counters or using the sysperfinfo(will read the perfmon counters)

    select * from sysperfinfo where object_name like '%mirror%'
    and instance_name = 'DatabaseName'

    Now check the counters Log Send Queue, Redo Queue etc

    3) Another way could be to generate the snap shot of the mirror & physically check the row count.

    Hope this helps !

    Thanks, Krishna.  www.Blogs.SQLServer.in 

    Wednesday, June 16, 2010 10:50 AM
  • Hi Krishna,

     

    Thanks for your reply. I tried using the sysperinfo but it is not working..Before issuing faiover command , i checked the counters. the values were 0. But after issuing the failover command, i am getting error message that the mirror server is not caught up to the recent changes. After i wait for some time and re-issue the failover statement, the failover is taking place

    Wednesday, June 16, 2010 6:08 PM
  • Hello,

    The table name is sysperfinfo. you missed "f" i guess.

    Looks like the log file is big in the Principal server. It may take time to sync with the mirror database. This normally happens on bulk inserts.

    Thanks, Krishna. www.Blogs.SQLServer.in

     

    Thursday, June 17, 2010 11:47 AM
  • hello krishna,

    in my previous post , there was typing error..i used the sysperfinfo table...so, is there no way to confirm if the mirrror server is caught up to the recent changes before issuing the failover command

    Thursday, June 17, 2010 2:56 PM
  • Kiran,

    Pls let me know your mirroring type. If its asynchronous, you cannot do the failover manually. In this case you will have to do the forced failover through sql query.

    Eg :- ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.

    Note that, this may involve potential data loss on the mirror, since the logs from the principal might not have reached the mirror.

    Hope this helps !

    Thanks, Krishna. www.Blogs.SQLServer.in

     

     

    Friday, June 18, 2010 8:56 AM
  • hi Kiran, the below system-view query can be run on both principal and on mirror to check if mirror has caught up with recent changes (mirroring_failover_lsn column):

    {

    SELECT
          DB_NAME(database_id) AS 'DatabaseName'
        , mirroring_failover_lsn
    FROM sys.database_mirroring

    }

    Sunday, June 27, 2010 10:04 AM
  • Also, failover with fail with error 1422 "The mirror server instance is not caught up to the recent changes to database "%.*ls". Unable to fail over." if mirror instance was not able to catch up with changes on principal i.e. if failover has happened (without error), its a good indication that mirror has already caught up with changes on principal.
    Sunday, June 27, 2010 10:09 AM