Wednesday, December 29, 2010

Error message 14421 that occur when you use log shipping in SQL Server

Error: 14421, Severity: 16, State: 1


The log shipping secondary database NY-SQLSRV001. AdventureWorks has restore threshold of 45 minutes and is out of sync. No restore was performed for 150 minutes. Restored latency is 1154 minutes. Check agent log and logshipping monitor information.
 
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.

Solution:
1. Disable LogShipping Settings on user Database AdventureWorks from Primary Server.
2. Take the Full backup of user database AdventureWorks from Primary Server
3. Copy the backup file to Secondary server Shared Network folder
4. On Secondary server open the Query Report to run the following statement
-- Recover a Database from a Backup Without Restoring Data
-- Restore database using WITH RECOVERY.


RESTORE DATABASE AdventureWorks WITH RECOVERY
5. Now the User database AdventureWorks allow you to restore with NORECOVERY mode
6. Restored the AdventureWorks on Secondary Server from Backup file
7.Setting up Logshipping on Primary Server to Secondary server

No comments: