July 4, 2011 at 7:48 am
im testing a few items in log shipping, but getting it started on my local pc is turning out to be quite difficult.
I have two instances, instance1 - primary, instance2, the standby .
instance1 is backing up the log every minute.
The copy job runs every minute also.
The restore job runs every 25 seconds.
It was an hour or two between setting up the backup jobs, and the copy jobs, so i after i copied the full backup and restored it on the standby instance, i had 250+ trn logs to copy and backup.
All are copied over correctly, but when the restore job tries to restore them it just says "skipped log backup file "path..\..\filename.trn"
There is no further information.
Anyone know what is happening here? A few points to consider:
1. I know that the most of these logs contain absolutely no changes ( as the database is on my local machin only i make any changes.
2. could it be skipping across them as they contain no new data? it still should eventually hit a file that has new data and import that, but its not.
3. should i be removing backup files from the destinationShare once the job has read them?
July 4, 2011 at 8:35 am
winston Smith (7/4/2011)
The restore job runs every 25 seconds.
Hmm, Hours or Minutes are the only frequency options for a job schedule!!!
can you post details of the backup, copy and restore jobs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 4, 2011 at 8:42 am
Perry Whittle (7/4/2011)
winston Smith (7/4/2011)
The restore job runs every 25 seconds.Hmm, Hours or Minutes are the only frequency options for a job schedule!!!
can you post details of the backup, copy and restore jobs
For the UI yes, but in the script you can go down to seconds and even MS iirc.
July 4, 2011 at 9:34 am
yes quite right, can see the SP code offering hour minutes or seconds.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 4, 2011 at 1:34 pm
To be completely sure i had processed all the backups I:
1. disabled the copy job. this ensured i had a finite known number of trn log backups to restore to the secondry.
2. Disabled the backup job.
2. ran the restore job until it ran through all the backups ( skipped all of them).
3. archived off the backups.
4. run the backup job manually to give me 1 trn log backup, copy it using the copy job and restore it using the restore job.
Once again the latest backup is "skipped", and the job tells me "2011-07-04 20:28:53.91 Could not find a log backup file that could be applied to secondary database 'AdventureWorks'. ",
I know i am not missing any trn backups, as i have the same number in my backup share as in my copy share.
Anyone got any ideas?
July 4, 2011 at 2:00 pm
I took a new backup and restored it to the secondry, and now my trn backups are restoring, so im guessing it was a screw up on my part, and i restored the wrong backup initially. thats all i can think of that would cause the issue in question.
Thanks for the help folks.
July 4, 2011 at 2:19 pm
FYI, a few points i have learned that are useful as they dont appear in any books ive read:
1. If you have 200 trn logs in your destination share, and you stop and restart the restore job, it will start at the beginning with the oldest trn log backup, even if it processed and skipped it the last time. unsure if this happens if you let the job run its course and rerun on schedule. i suspect it is.
For this reason, if you are doing log backups very frequently, you should be moving the files from the destination share. I wonder why the restore job does not have an archive functionality that would archive the processed backups in a specified share.
2.the restore job wont restore logs that do not contain any changes. I tested this by running the backup job 3 times, to give me 3 trn backups with no changes.
I then made some changes and took another trn backup.
Once these trn backups were copied over, the restore job skipped the first 3 trn log backups but restored the last one, preseumably because it was the only backup with changes ( would like to confirm this though).
3. Even with sub 30 second backups, log shipping still takes significant time to copy and restore the logs, especially if there are a few of them, even with no changes as it has to read all the logs to check if they have changes. Even with jobs working on 30 second schedules, it could take up to 4 mins to see the changes on the secondry.
Thanks for the help all.
July 4, 2011 at 4:29 pm
Wow, amazing tips. I'll keep that in mind if I ever set that up!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply