August 28, 2012 at 7:29 am
Hi all,
Just set up log shipping. I get no errors from the agent jobs and can see the files in the secondary restore location. But when i do query i dont see any new info.. Been running for 4 hours (Log shipping hourly). Ive seen it, where it updates show up an from the 1st log file when you do the second etc. but the no results is puzzling to say the least.. any ideas?
August 28, 2012 at 3:43 pm
Carefully check the history of the copy and restore jobs, my guess is you'll have some error messages on the restore job.
Do you have any trans log backups runnin on the primary database as part of a maint job?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2012 at 3:25 am
Hi,
Yes had a brain melt..
realized that i still had my ola scripts still doing backup logs... Ive removed and started again and seems to be working now.
It did give me some questions though.
What would happen if the WAN went down and a log file was skipped. Would log shipping still work? and it just skip that 1 hour of activity? or does it stop.
If it stops. How do you get it unto date (Manual restore of log files?)
If it just skips (which i wouldn't have thought it would) how do you put in the old log files.
Thanks for the reply.
August 29, 2012 at 8:07 am
n00bDBA (8/29/2012)
Hi,Yes had a brain melt..
realized that i still had my ola scripts still doing backup logs...
That's pretty much what i was expecting 😉
n00bDBA (8/29/2012)
What would happen if the WAN went down and a log file was skipped. Would log shipping still work? and it just skip that 1 hour of activity? or does it stop.
This depends on how long it was down for and the file retention period set on the primary.
If the WAN was down for 4 hours but the log backups are removed on the primary after 2 hours then yes, when the WAN came back the restore job would complain because the log backups its recently collected and trying to restore have a gap.
n00bDBA (8/29/2012)
If it stops. How do you get it unto date (Manual restore of log files?)If it just skips (which i wouldn't have thought it would) how do you put in the old log files.
Thanks for the reply.
If you get to this point and there are log backups missing due to the scenario mentioned above do the following;
Disable all LS jobs for this database (i.e. backup, copy and restore)
take a differential backup on the primary
restore the differential to the secondary
clear all log backups from the primary and secondary locations
Re enable LS jobs (backup, then copy, then restore)
If you get to this point and all the log backups are in place on the primary then just run the copy job followed by the restore job.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2012 at 4:57 am
Hi Perry thanks!
Can i just confirm im understanding it right.
CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down
10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary
CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down
Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary
Thanks for the help!
August 30, 2012 at 5:48 am
n00bDBA (8/30/2012)
Hi Perry thanks!Can i just confirm im understanding it right.
CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down
10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary
CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down
Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary
Thanks for the help!
Before looking at any of this wee need to know the thresholds you have set on the LS config for the database in question, can you supply these?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2012 at 6:42 am
hey,
the delete old log backups on primary and secondary is set to 72 hours.. Is that what you mean? I don't remember any other thresholds..
thanks 🙂
EDIT...
think before you type really needs to be drilled into my head! used the log shipping SP and saw a few others that makes more sense..
backup_retention_period - How Long the Log backup is kept on the Primary Server (I picked 72 hours)
backup_threshold - 120 - Will trigger an alert if a log backup isnt performed within this time period? (I picked 1 hour 15 mins if ive set backups to 1 hour.. Would this effect things..
On Secondary I can see
restore threshold - set an alert if no restore is done within this time frame (for my 1 hour backups i set it to 75 mins again
file retention_period which i again set to 72 hours.
Another question this brings up (Keep questioning if im looking at this right!) Ive set the Log backups on the Primary to be every hour from 00:30
The Copy ive set to be 10 mins after this (just to make sure the file has been created)
The restore ive left as default which is 15 mins.. since i didnt know how long the copy would take and having it keep trying seems a safer bet.
Does that seem right or is it foolish!?
Thanks for the help (And patience!)
August 30, 2012 at 7:28 am
n00bDBA (8/30/2012)
Hi Perry thanks!Can i just confirm im understanding it right.
CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down
10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary
CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down
Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary
Thanks for the help!
72 hours, so
in case 1 the copy and restore jobs would not have any affect as the copy job would not be able to access the primary backup share (you'd probably get an error). After an hour when the link is back up, the copy job would see the new files on the primary backup share and copy them across to the secondary. The restore job will restore any new backups that it finds.
in case 2 the same thing would happen in that the copy job would not be able to access the primary backup share. Now, if it's a failure that you are aware of you would stop the 2 jobs on the secondary or just leave them to run expecting no action and manually retain the log backups by adjusting the purge threshold. If you're not aware then the jobs will just run as usual and do nothing anyway.
Assuming you aren't aware of the failure do this
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log backup files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - execute LS Log backup job on Primary
Tue 9:31 - execute LS Copy job on Secondary
Tue 9:35 - execute LS Restore job on Secondary
LS should carry on as usual
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 30, 2012 at 8:02 am
Thanks sir
clears up a lot of questions i had.. and not really related to the starting question so thanks for sticking with me!
August 30, 2012 at 8:15 am
it all depends on whether the failure is known or not. Try it on a test system you'll see how it works
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply