November 18, 2010 at 1:04 am
Dear All,
I'm new to sql dba, plz tell me if logshipping secondary server restore job fail how to reslove this issue ......Please guide me advanced Thanks
My error is
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 log shipping monitor information.
November 18, 2010 at 1:11 am
That's not a restore job failure message. That's saying that there has been no restore for a certain period of time. If the restore job is failing, check the reason for that job's failure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 1:14 am
You need to find out which particular log backup file didn't get restored. If you cannot dig into what caused the failure, simply take a backup of production and restore it on secondary in norecovery/standby mode. it'll be in sync.
November 18, 2010 at 2:29 am
suppose my database size 200Gb then also i 'll do same way na..? backup/restore
November 18, 2010 at 2:31 am
Thank's
November 18, 2010 at 2:31 am
yes. It would be better if you zip the backup and then copy. this would reduce copy time.
November 18, 2010 at 3:11 am
There is nothing in the posted error that indicates that the restore chain is broken or that the log shipping database needs to be restored from another full backup.
All that message says is that there has not been a restore for a certain period of time. There is no reason given for that.
Before anything radical is done (like copying full backups to restore), find out why there has not been a restore for a certain period.
Are the log backups not being copied?
Are the log restores failing? If so, with what error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 6:31 am
@ rajesh..
As gail said, its important to analyze the error rather than just backup and restore again,
What will you do if you again get this type of error after restoring..?? again backup/restore???
Look for the jobs whcih are failing, Are the logs copied to secondary or not..
If they are being copied then, check the restoring jobs are failing....why are they failing...
I have some insights..:-
--> There can be some errors due to the wrong timing set in log shipping configuration. These timing could be of alert jobs which always should be set according to the backup and restoring jobs frequency.
-->You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, we 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, we must modify the value of the Out of Sync Alert threshold accordingly.
--> Moreover there can be errors like the copying of transaction log files at the primary server side.
We have to ensure that the transaction logs are being copied rightly according to the schedule.
Same thing on the secondary server too, the restoring of transaction logs should be ensured properly.
We have to look into the job history of copying and restoring jobs on both servers to take this into account
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 18, 2010 at 6:39 am
@ rajesh
Also,
--> When the logs are shipped to the secondary server but they are not applied on to the secondary database. That chain of unapplied logs increases and log shipping goes more out of sync. In that case the logs have to be manually applied to the secondary database according to the sequence of occurrence. We have to see the last transaction log been applied on the database and then go to the physical location where the logs are copied on the secondary server.
We can see the transaction log which is not applied to the database and choose it manually. The restore jobs on secondary server can tell us which transaction log, we applied latest and was successful
If, you cant get anything from errors, then backup restore should be your last weapon.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 20, 2010 at 1:41 am
Yes..firstly we need to find the root cause..
Identifying why the restore has failed- check the last copied file that has been restored in the tables(using msdb queries)..
1) ..see the number..if it goes as only few files have not been restored then we can perform manual restore of them and then we find the LS in sync..
2).. check whether the restore job has been failing since long, ever from its creation..check the job step..inspect the query..if incase the query is changed by mistake..edit it and run restore again..If the job itself is not configured properly..LS needs to be resetup..
3)..If the restore alone has been failing since long and the backup job and copy are running successfully then do DCL..
Dump Copy Load..
Take a full backup, copy to secondary and restore..
Provided be sure to disable the jobs namely backup, copy restore...
These are few things we do while we encounter such issues...
Santosh Vishal Adiraju.,
November 26, 2010 at 12:44 am
Thank's Bro
November 26, 2010 at 12:45 am
Thank's Dude
December 16, 2010 at 9:34 am
Well thats my pleasure..Iam glad my post could be of help!!..
regards
Santosh Vishal Adiraju.,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply