SQL Server Performance

Out of Sync Error

Discussion in 'SQL Server Log Shipping' started by shekhar157, May 18, 2007.

  1. shekhar157 New Member

    Hi Folks,

    I am having a job "Log Shipping Alert Job - Restore" on the server where I have set up the log shipping for production box. Some time it is giving me the error like
    "Executed as user: User_Name. The log shipping destination Server_Name.DB_Name is out of sync by 135 minutes. [SQLSTATE 42000] (Error 14421). The step failed." My question is if the server is getting out of sync then why job again execute successfully. The job is scheduled in every five minutes. Some time its giving me the error and sometime it is running successfuly. Can anyone help me on this.

    Thanks in Advance,

    Shekhar Mishra

    "No passion in the world is equal to the passion to alter someone else's draft."

  2. satya Moderator

  3. shekhar157 New Member

    Satya,

    As per KB in point 4 for alert "Troubleshooting Error Message 14420"

    --You may have set an incorrect value for the Backup Alert threshold. Ideally, you must set this value to at least three times the frequency of the backup job. If you change the frequency of the backup job after log shipping is configured and functional, you must update the value of theBackup Alert threshold accordingly.

    Our job is running in every 5 minute and we have define Alert ThreshHold as 45 Minute. So just for understanding how we can prevent these alert coz its going to some application guys and they assuming it as a error. So for explaing to them can we give them some points.

    Thanks,

    Shekhar

    "No passion in the world is equal to the passion to alter someone else's draft."

  4. shekhar157 New Member

    FYI,

    We also have some other DBs Log Shipping from the same production box and the target is same. Configuration for all log shipping same. But we getting the alert for this DB only.

    "No passion in the world is equal to the passion to alter someone else's draft."

  5. satya Moderator

    Have you check all the resolution points on KBA 329133?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. shekhar157 New Member

    Yes its asking for Latest Service Pack and we have SP4 on both the boxes.

    "No passion in the world is equal to the passion to alter someone else's draft."

  7. satya Moderator

    I mean to say check
    quote:
    There are serveral reasons why the alert message is raised. The following list includes some of these reasons: 1. The date or time (or both) on the primary server is modified such that the date or time on the primary server is significantly ahead between consecutive transaction log backups.
    2. The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.
    3. You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly.
    4. Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.
    5. It is also possible that the Restore job on the secondary server is failing. In this case, check the job history for the Restore job because it may indicate a reason for the failure.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. shekhar157 New Member

    Hi Satya,

    I have checked all the stuffs what you mentioned, only there is some time difference in seconds on both the server but the threshold time is 45 min and the job is schedule in every 5 minute. So I think that can not have any effect to fire these alert. The alert is firing only after having out of sync 135 min. I am not able to understand where is the problem now. BTW the DBs are in sync and there is no problem with the log shipping. These alerts is only creating the headache.

    Thanks,

    Shekhar

    "No passion in the world is equal to the passion to alter someone else's draft."

  9. satya Moderator

    As mentioned they are warnings not a real issue for the log shipping, if you have any Management software then ignore them simply.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. shekhar157 New Member

    Sorry for continuing this discussion as you mentioned that there is no problem with the log shipping. But for other DBs the source and destination servers are same. But only for this DB why we are getting these alerts. If some probe or warnings are generating then why not for other DBs.

    Shekhar

    "No passion in the world is equal to the passion to alter someone else's draft."

  11. satya Moderator

    Not a problem the forums are here help you.

    There might be a problem with the backup history files in msdb created due to log shipping backup restore happening every x minutes. Check and use sp_delete_backuphistory to clear all the backup history see again.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page