[MSSQL] Log Shpping Error Message ... LS Alert

가끔씩 이런 메시지를 목격하곤 합니다. 
아래방법은 MSDN에서 찾은 해결방법입니다. 

그냥 참고... 

Description of error message 14420 and error message 14421 that occur when you use log shipping in SQL Server

View products that this article applies to.

This article was previously published under Q329133

On This Page

Expand all | Collapse all

SUMMARY

This article discusses the reasons for "out of sync" error messages when you have log shipping configured for SQL Server 2000. 

One of the following error messages may be logged in the SQL Server error log:

Error message 14420

Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

Error message 14421

Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

If you are using SQL Server 2005, the description for these error messages are different:

Error message 14420

Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.

Error message 14421

Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.

Back to the top

MORE INFORMATION

Log shipping uses Sqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates thelog_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates thelog_shipping_secondaries table. 

As part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity. The alert messages are generated depending on the value of Backup Alert threshold and Out of Sync Alert threshold respectively.

The alert message 14220 indicates that the difference between current time and the time indicated by the last_backup_filename value in thelog_shipping_primaries table on the monitor server is greater than value that is set for the Backup Alert threshold.

The alert message 14221 indicates that the difference between the time indicated by the last_backup_filename in the log_shipping_primaries table and thelast_loaded_filename in the log_shipping_secondaries table is greater than the value set for the Out of Sync Alert threshold.

Back to the top

Troubleshooting Error Message 14420

By definition, message 14420 does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold.

There are serveral reasons why the alert message is generated. The following list includes some of these reasons:

1. The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages.
2. When the monitor server is offline and then back online, the fields in thelog_shipping_primaries table are not updated with the current values before the alert message job runs.
3. The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in thelog_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.
4. 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.
5. The backup job on the primary server is failing. In this case, check the job history for the backup job to see a reason for the failure.

Back to the top

Troubleshooting Error Message 14421

By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.

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 thelog_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.

Back to the top

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

314515  INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping

321247  HOW TO: Configure Security for Log Shipping

Back to the top

---

APPLIES TO

Back to the top

Keywords:

kbhowto kberrmsg KB329133

Back to the top