Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you how to solve the 14420 and 14421 errors of SQL Server 2005. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Error message 14420

Error: 14420 severity: 16, status: 1

Log transfer master database% s.% s has been backed up in% d minutes and does not have backup log operations within% d minutes. Check the agent log and the information on the $logshipping monitor.

Error message 14421

Error: 14421 severity: 16, status: 1

The log shipping secondary database% s has% d minutes with a restore threshold and is out of sync. There is no restore in d minutes. The restored lag time is d minutes. Check the agent log and the information on the $logshipping monitor.

Log shipping uses Sqlmaint.exe to back up and restore the database. When SQL Server creates a transaction log backup as part of the log shipping settings, Sqlmaint.exe connects to the monitoring server and updates the log_shipping_primaries table with last_backup_filename information. Similarly, when you run a replication or restore job on a secondary server, Sqlmaint.exe connects to the monitoring server and updates the log_shipping_secondaries table.

As log shipping alert messages 14220 and 14221 are generated to track backup and restore activity. Depending on the value of the backup alert, the alert message threshold and the out-of-sync alert threshold are generated respectively.

Alert message 14220 indicates that the time difference between the current time and the time in the log_shipping_primaries table on the indicated last_backup_filename value monitoring server is greater than the value set for the backup alert threshold.

The alert message 14221 indicates that the time difference between the last_backup_filename in the log_shipping_primaries table and the last_loaded_filename in the log_shipping_secondaries table is greater than its value and is set to an out-of-sync alarm threshold.

Troubleshooting error message 14420

By definition, message 14420 does not necessarily indicate that there is a problem with log shipping. This message indicates that the difference between the last backed up file and the current time on the monitoring server is greater than the time threshold set for the backup alert.

There are several reasons why an alert message will be generated. The following list includes some of these reasons:

Date or time (or both) on the monitoring server is a different date or time on the primary server. It may also be that the system date or time has been modified on the monitor or on the primary server. This may also generate alert messages.

When the monitoring server is offline and then returns to the online status log_shipping_primaries table the fields are not updated with the current value before the alert message job is run.

It is not possible for log shipping replication jobs running on the primary server to connect to the monitoring server msdb database to update fields in the log_shipping_primaries table. This may be due to authentication problems between the monitoring server and the primary server.

You may have set an incorrect value for use in the backup alert threshold. Ideally, you must set this value to the frequency of at least three backup jobs. If you change the frequency of backup jobs, log shipping configuration, and work properly, you must update the value of the backup alert threshold accordingly.

Backup job on the failed primary server. "to check the reason for the failure in this case, refer to the job history of the backup job."

Go back to the top

Troubleshooting error message 14421

By definition, message 14421 does not necessarily indicate that there is a problem with log shipping. This message indicates the last differential backup of the file, and that the last restored file was greater than the selected time for the out-of-sync alert threshold.

There are a number of reasons for causing alert messages. The following list includes some of these reasons:

A continuous transaction log backup between the date or time (or both) modified on the primary server or significantly in advance of the time on the primary server.

The restore job run by the log shipping secondary server cannot connect to the monitoring server msdb database to update the correct values in the log_shipping_secondaries table. This may be due to authentication problems between the secondary server and the monitoring server.

You may have set the out-of-sync alert threshold to an incorrect value. Ideally, you must set this value to the slower copy and restore job in at least three frequencies. If the frequency of the replication or restore job modifies the log shipping settings and functionality, you must modify the value of the out-of-sync alert threshold accordingly.

Problems with using backup or replication jobs are most likely to cause "out of sync" alert messages. If an out-of-sync alert message is raised and there is no problem, use a backup or restore job, check for potential problems with the replication job. In addition, a network connection may cause replication jobs to fail.

It is also possible that the restore job on the second server failed. In this case, check the job history of the restore job, as it may indicate the reason for the failure.

The above is the editor for you to share how to solve SQL Server 2005 14420 and 14421 errors, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.