Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

SQL Server world: Learning and Sharing my experience & knowledge ....
Browse by Tags · View All
sql server 51
best practices 39
#SQL Server 28
dba 25
BRH 19
beginners 17
download 15
#TSQL 15
dba practices 9
#DBA 9

Archive · View All
September 2010 13
August 2011 5
October 2010 5
November 2011 4
October 2011 4
May 2011 4
February 2011 4
November 2010 4
January 2012 3
July 2011 3

SQL Server Manageability: Log Shipping Error message 14420 and error message 14421

Mar 9 2011 2:17AM by Satya Jayanty (@sqlmaster)   

SQL Server Log Shipping is one of the essential task that every user must setup to offer disaster recovery (standby) for the databases. Since the version 2005 it has become easier and quicker way to setup using SQL Server Management studio.

Almost all the editions of SQL Server offer the log shipping setup and configuration via SSMS except the SQL Express, SQL Express with Tools & SQL Express with Advanced Services editions. Using the TSQL (script) methodology you can accomplish this tasks refer to Brad's article.

Once the Log Shipping is configured then the next task is to ensure the transaction logs are getting transfered as per the setup which requires a monitoring strategy. By default MSDB database maintains such information as a history & status of log shipping operations including the scheduled jobs. This information is stored on the primary server and history and status of copy/restore operations are stored on the secondary server, in addition to PRIMARY and SECONDARY servers if you have opted an additional server as LOG SHIPPING MONITOR server then all the above information is stored on the monitor server. The best practice dictates that for monitoring purpose you must configure alerts that will fire if a certain log shipping operation fail to occur as per the schedule, by default the errors raised by an alert is logged on SQL Server error log that helps the pre-installed monitoring software such as SCOM to pick up and send alerts. However certain alerts for scheduled jobs where the errors are raised due to status of backup or restore operation, you can manually setup alerts that notify an operator when such errors are raised. In addition to the log shipping pair if a LOG SHIPPING MONITOR server is configured then such alert jobs are executed on monitor server that will raise errors for all operations in log shipping configuration, otherwise such alert job is executed on PRIMARY server.

Digging deeper into the managemability of log shipping, when it is used

thenerror message numbers specified in subject line will be familiar. 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.
 Still there is a refinement in the error message (not solution) to understand :
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: 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.
A brief note on major change in the Log Shipping between 2000 and 2005 versions is, within SQL 2000 version log shipping uses Sqlmaint.exe to back up and to restore databases. The usual BACKUP LOG statements are passed that  creates a transaction log backup as part of a log shipping setup. Also this Sqlmaint.exe process connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. A change of process when you perform a COPY or RESTORE job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.
More often I see within forums & newsgroups users asking for solution when it occurs in their environment, thinking it is a problem in log shipping. Actually it is not, as a part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity, so you will need to set the backup-alert & out-of-sync thresholds depending upon the backup interval between primary and secondary servers.
The message 14220 refers the difference between current time and time that indicates the last_backup_filename value stored on LOG_SHIPPING_PRIMARIES table within the Log Shipping Monitor server, greater than value that is set for the Backup Alert threshold. Similarly message 14221 indicates the time difference between the last_backup_filename in LOG_SHIPPING_PRIMARIES table and last_loaded_filename in LOG_SHIPPING_SECONDARIES table, which will be greater than the value set for the Out of Sync Alert threshold.
Microsoft documentation & BOL indicates that both of these messages does not necessarily indicate a problem with log shipping, but still if it occurs then you must check the schedule of log backup & restore. As 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. Within my experience I see this occurs due to the time difference between Primary & Secondary server including the Log Shipping Monitor server, if you have setup on seperate instance. Also if there is any issue within the Log Shipping MONITOR server such as you restart it during any hotfix or patching of operating system, then the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. Recently within a DR based SQL Server I have seen that one of the transaction log backup job has failed due to the some non-logged operations and DBA executing BACKUP LOG ... WITH NO_LOG within the scripts causing the  
backup job on the primary server is failing and to resolve this I have checked the job history for the backup job to see a reason for the failure.

Similarly the message number 14421 does not necessarily indicate a problem with Log Shipping, as it refers that difference between the last backed up file and last restored file is greater than the time selected 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. 8 out of 10 times I see this message is generated due to the problems within the BACKUP job and mostly during the COPY job from Primary & Secondary servers, causing to result in "out of sync" alert messages. Say if you have checked the job of backup & copy of file between servers has not issues then it is nothing bu network connectivity during copy job to fail.
FOr further monitoring the key log shipipng monitoring tables are: log_shipping_monitor_alert, log_shipping_monitor_error_detail and log_shipping_monitor_history_detail. The inter-operability of Log Shipping with Database Mirroring is possible, where the PRINCIPAL database in a MIRRORING session can act as a PRIMARY database in log shipping configuration for the backup share. However the database mirroring session can run in any operation mode such as synchronous *(transaction safety = FULL) or asynchronous (transaction safety = OFF). A note that during a log shipping session all the backup jobs on the primary database creates a log backups in a backup folder, from there the log shipping will pickup the files to copy those files to secondary server. In case of backup jobs and copy jobs to succeed, the SQL agent service account must have access to the log shipping backup folder and for further availability of the PRIMARY server ensure to establish a backup folder in a shared backup location that is hosted on seperate server and if a LOG SHIPPING MONITOR server is involved then log shipping pair must have access on that shared backup location.
Here is the explanation about failover of database mirroring partners when log shipping is involved (source:Books Online):
Log shipping and database mirroring

After a mirroring failover, the primary server name defined on the secondary server is unchanged. .

Tags: best practices, sql server, BRH, high availability, database mirroring, #sql, dataabase recovery, #ha, manageability practices, log shipping,

Satya Jayanty (@sqlmaster)
34 · 5% · 1720


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]