August 16, 2009 at 12:30 pm
Hi,
I have configured the Log shipping between primary & secondary(both locations are in same Time Zone) in SQL Server 2005 EE x64 with SP3.I have used the Log shipping configuration wizard to setup. Here are the configuration settings:
1. Backup job runs every 1 hrs
2.Delete files older than 72 hrs
3.Alert if no backup occurs with in 2 hrs
4.Copy job runs every 1hrs
5.Delete files older than 72 hrs
6.Restore job runs every 1hrs
7.Delay restoring backups at least 30 mins
8.Alert if no restore occurs with in 60 mins.
Now I'm getting out of sync error as below in the Alert job on the Secondary
Message
Executed as user: abc\mssql. The log shipping secondary database sql2.Mydb has restore threshold of 60 minutes and is out of sync. No restore was performed for 61 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed
.
And I went through the link http://support.microsoft.com/kb/329133 and its saying
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
.
But I did not found the the option to set Out sync value in the Log shipping wizard!!
Please advice whether the above settings correct? or do I need to change any settings?
Do I need to change the Alert if no restore occurs with in 60 mins to 180 mins(3 times the restore job schedule??) to avoid the above error??
thanks
August 16, 2009 at 10:09 pm
Could you please advice me...
August 17, 2009 at 12:07 am
1. Backup job runs every 1 hrs
2.Delete files older than 72 hrs
3.Alert if no backup occurs with in 2 hrs
4.Copy job runs every 1hrs
5.Delete files older than 72 hrs
6.Restore job runs every 1hrs
7.Delay restoring backups at least 30 mins
8.Alert if no restore occurs with in 60 mins.
In your scenario let's assume the backup starts at 1PM and completes at 1:30PM now the copy task will pick this file at 2PM because it runs every 1 hr then the copy task took 5 minutes so the Restore job will pick this backup at 3PM and apply it.
To correct this Change the timing
Copy job runs every 30 minutes
and
Restore job runs every 30 minutes
and
Alert if no restore occurs with atleast 90minutes
and change the other settings accordingly.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 17, 2009 at 12:11 am
Team pls. share your views.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 17, 2009 at 6:45 am
You also have to consider the time the jobs run and that 30 minute delay you have set.
Lets say you leave them all running every hour.
Your backup starts at 1:00, your copy job at 1:15, your restore job at 1:30. 30 minute delay puts you outside 1 hour.
So what are you looking for? You want 1 hour difference between the servers? How much can you afford to lose if you manually fail (bring online) your secondary? What time do your jobs run?
EDIT-
--Fixed something I wrote incorrectly.
August 18, 2009 at 10:32 pm
Lets say you leave them all running every hour.
Your backup starts at 1:00, your copy job at 1:15, your restore job at 1:30. 30 minute delay puts you outside 1 hour.
I have set the restore delay as 30 mins But what I observed is having the restore delay 30 mins not effecting anything. See my below explanation for the above comment and correct me if I understood wrong.
Backup runs job every 1hr lets say starting at 8:00 AM (Mydb_date.trn)
copy job runs every 1 hr lets say starting at 8:05 AM (copied to secondary server Mydb_date.trn)
Restore job runs every 1hr lets say starting at 8:35 AM and it restored the Mydb_date.trn.
But I have 30 mins restore delay and Ideally it should not restore Mydb_date.trn file at 8:35 AM right? So it should restore at next Restore job runs at 9:35 AM right?
then what is the purpose of restore delay of 30 mins here? how it is or has to work in my case?
please clarify me about this restore delay concept?
Here is what actually happened on my servers:
1. Backup job ran at 8:45 PM . Here is the transaction log backup file Mydb_20090819034501.trn (date format is in UTC standard)
2. Copy job ran at 8:47 PM . Message from copy job view history:
Message
2009-08-18 20:47:47.32Renamed temporary work file. Source: '\\sqlvs\Logshipping_Tlogs\Mydb_20090819034501.wrk', Destination: '\\sql2\Logshipping_Tlogs\Mydb_20090819034501.trn'
3. Restore job ran at 9:30 PM.
Message
2009-08-18 21:30:00.93Retrieved database restore settings. Secondary Database: 'Mydb', Restore Delay: 30, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: \\sql2 \Logshipping_Tlogs\Mydb_20090819024500.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
Message
2009-08-18 21:30:07.26Restored log backup file. Secondary DB: 'Mydb', File: '\\sql2\Logshipping_Tlogs\Mydb_20090819034501.trn'
2009-08-18 21:30:07.27The restore operation was successful. Secondary Database: 'Mydb', Number of log backup files restored: 1
2009-08-18 21:30:07.31Deleting old log backup files. Primary Database: 'Mydb'
2009-08-18 21:30:07.34The restore operation was successful. Secondary ID: '6ef46c75-c335-467d-b7e3-6e87851276a8'
2009-08-18 21:30:07.35----- END OF TRANSACTION LOG RESTORE -----
Exit Status: 0 (Success)
and also from error log:
2009-08-18 21:30:07.22 Backup Log was restored. Database: Mydb, creation date(time): 2009/07/24(19:32:24), first LSN: 128084:1643:1, last LSN: 128086:8336:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\sql2\Logshipping_Tlogs\Mydb_20090819034501.trn'}). This is an informational message. No user action is required.
4. I have select restore delay as 30 mins, while configuring Log shipping
From above information:
How does the restore delay 30 mins worked? could you please explain me with an example?
August 19, 2009 at 10:31 am
the restore latency comes into picture only if we schedule all the 3 jobs Backup, copy & restore at the same time?
Is above statement correct?
thanks
August 20, 2009 at 12:18 pm
please advice me..
August 23, 2009 at 1:31 am
the restore latency comes into picture only if we schedule all the 3 jobs Backup, copy & restore at the same time?
Is above statement correct?
How restore latency works If I schedule backup, copy & restore jobs at different timings?
thanks
Ram
August 23, 2009 at 3:39 pm
I would say that is correct. the delay should set the restore off at 8:30 with a 30 minute delay and it scheduled at 8AM.
The logic needs to be
On the source site:
Assuming a full backup, copy, and restore has taken place
8:00 AM - log backup
8:05 AM - log copy
8:30 AM - log restore
If another log backup takes place before the 8AM log backup is restored, then your LSN's will be out of sync, resulting in failure.
I've only used custom scripts to implement log shipping, but the concept is the same, the backups all need to be restored in sequence.
We can afford a longer delay with our databases, and generally set up log backups at 4am, 8am, 12pm, 4pm, and 8pm, with a full backup at 12am.
Our restores take place at 1AM (full restore), 5am, 9am, 1pm, 5pm, 9pm.
Hopefully that info builds on your knowledge of log shipping.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply