none
SQL Logshipping alert from LSAlert_Secondary issue

    Question

  • Hello all,

    I am facing a strange issue with Log Shipping. Currently I have logshipping setup between primary and secondary database servers. The Backup job runs once every hour and I set the alert to notify me if a backup does not occur in 120 mins and the move job runs once every 30 mins and I set the alert to notify me if a copy does not occur in 60 mins and the restore job runs once every hour and I set the alert to notify me if a restore does not occur in 120 mins. After 2 days of setting up LS now I am getting errors from 'LSAlert_secondaryServer' once every 2 mins that the job failed.

    The view history job on this is showing me that a restore did not occur for all of the databases for a threshold longer than 120 mins. But if check the view history of actual restore jobs I see it completed successfully for all the databases all the time. similarly, the backup and move jobs completed successfully too.

    Has anyone faced same issue and is there a solution to fix these alerts. 


    Sunday, September 22, 2013 1:44 AM

Answers

  • Here is the error on LSAlert_secondaryserver:

    the database has restore threshold of 120 minutes and is out of sync. No restore was performed for 488 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

    WHats strange the restore completed successfully per the restore job.

    Hello,

    Use below query to find out what was last restored trn log backup

    ----rn this on secondary DB
    use msdb
    go
    select * from dbo.log_shipping_monitor_secondary
    
    ---for backup log information on primary
    use msdb
    go
    select * from dbo.log_shipping_monitor_primary

    Follow this MS article and troubleshoot your Issue.

    http://support.microsoft.com/kb/329133


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, September 22, 2013 5:25 AM
  • Hello,

    The issue can also occurs when there is a redundant transaction log backup jobs on the primary server which causing the sequence to be broken or the recovery model of the database was probably toggled between transaction log backups.
    You can review the transaction log backup history on the primary server. If there is a backup Log was running out of logshipping process, restore the backup on the secondary server.

    Reference:http://blogs.technet.com/b/mdegre/archive/2009/08/11/logshipping-secondary-server-is-out-of-sync-and-lsrestore-job-failing.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Monday, September 23, 2013 2:09 AM
    Moderator

All replies

  • Here is the error on LSAlert_secondaryserver:

    the database has restore threshold of 120 minutes and is out of sync. No restore was performed for 488 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

    WHats strange the restore completed successfully per the restore job.

    Sunday, September 22, 2013 2:04 AM
  • Here is the error on LSAlert_secondaryserver:

    the database has restore threshold of 120 minutes and is out of sync. No restore was performed for 488 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421).  The step failed.

    WHats strange the restore completed successfully per the restore job.

    Hello,

    Use below query to find out what was last restored trn log backup

    ----rn this on secondary DB
    use msdb
    go
    select * from dbo.log_shipping_monitor_secondary
    
    ---for backup log information on primary
    use msdb
    go
    select * from dbo.log_shipping_monitor_primary

    Follow this MS article and troubleshoot your Issue.

    http://support.microsoft.com/kb/329133


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, September 22, 2013 5:25 AM
  • Hello,

    The issue can also occurs when there is a redundant transaction log backup jobs on the primary server which causing the sequence to be broken or the recovery model of the database was probably toggled between transaction log backups.
    You can review the transaction log backup history on the primary server. If there is a backup Log was running out of logshipping process, restore the backup on the secondary server.

    Reference:http://blogs.technet.com/b/mdegre/archive/2009/08/11/logshipping-secondary-server-is-out-of-sync-and-lsrestore-job-failing.aspx

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Monday, September 23, 2013 2:09 AM
    Moderator