October 26, 2010 at 9:19 am
Hi,
My prod database is in logshipping.There is a job that runs weekly.This job reindexes the database.As a result the log gets tremendously large.and because of that the restore job fails.
what can be done in this case.
1.Can we decrease the duration of the log backup so that there will be more trn files but of smaller size.Will it matter.
2.We can create a maintainence plan.
1.set the database to simple.
2.reindex job.
3.set the database to full.
4.take full backup.
5.disable log-shipping jobs.
6.restore the full backup
7.enable all jobs.
Can it be done or am i being foolish.
October 26, 2010 at 12:31 pm
As a result the log gets tremendously large.and because of that the restore job fails.
1.Can we decrease the duration of the log backup so that there will be more trn files but of smaller size.Will it matter.
It may matter, but if the growth of your trn files is during the index maintenance, you can try to tune your index-maintenance plan.
1.set the database to simple.
2.reindex job.
3.set the database to full.
4.take full backup.
5.disable log-shipping jobs.
6.restore the full backup
7.enable all jobs.
That seems to be setting up the log-shipping over and over, every time you do an index maintenance. You can probably just focus on your index maintenance. Do you do a full index rebuild everytime the maintenance run? There are a lot of cases that if you use the maintenance plan out of the box, it does a rebuild for all indexes even if you don't need to. If you're not doing the script with smart check-yet(decide whether there's a need to reorganize or rebuild or skip the index), i suggest you consider it. There's a lot of site that provides a script for it, here's one to start with (this is a bit old already, but you'll get the idea):
October 26, 2010 at 10:08 pm
thanks for the reply....but what if there is a planned bulk insert.what can be done in that case
October 27, 2010 at 2:35 pm
@ azad...
r u running ur log shipping 24 hours??
u can skip for 5-6 hours everyday and during that time you can run this reindexing job..
Mine works like this only and it is running fine !!
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 27, 2010 at 2:53 pm
SKYBVI (10/27/2010)
@ azad...r u running ur log shipping 24 hours??
u can skip for 5-6 hours everyday and during that time you can run this reindexing job..
Mine works like this only and it is running fine !!
Regards,
Sushant
That's some brave advice to just toss out there without knowing more about the environment. If you have a database that experiences even a moderate amount of transactions, you could have a real mess on your hands by shutting log shipping off for 5-6 hours. Besides, if you are using LS for DR then shutting your LS job off for 5-6 hours is a bad idea, as you'd have to wait for the LS db to process 6 hours worth of txns before data is available.
What's confusing is (at least in 2008... could be different in 2005), if the LS db goes out of sync, yes the alert job barks about it being out of sync but the log shipping restore job should eventually catch back up with the primary server.
My advice is to pay more attention to what you are reindexing and why (a reorg may be more appropriate), and address that first and foremost. Then, address the duration settings on the log shipping job to possibly have the job execute more frequently, and hence less work each time. Then, wade through your error logs and possibly post back if you see that your log shipping database goes out of sync and STAYS out of sync.
October 27, 2010 at 9:52 pm
I appreciate your solution.But you said that perhaps shorten the duration of log backup.will it help.will i get more trn files but of small sizes.
October 28, 2010 at 6:14 am
azadsale (10/27/2010)
I appreciate your solution.But you said that perhaps shorten the duration of log backup.will it help.will i get more trn files but of small sizes.
Right - If you have the log apply job kicking off every 15 minutes instead of every hour, it will have less work each time and will help "smooth out" the work spikes. Still, as advised by others as well, look into doing index reorgs instead of full index rebuilds, according to how fragmented the index is.
In short though, I wouldn't shut off log shipping because of a work spike - You'd either have to restore the log shipping db from a full backup of primary again afterwards or let it catch up on eating logs later on anyway.
October 28, 2010 at 6:28 am
We have a similar situation with conducting a bulk load on a database that is involved with "Log Shipping".
DROP ALL INDEX
TRUNCATE TABLES
BULK LOAD
CREATE ALL INDEX
This is common practice with bulk loading. The time it took to apply the logs after this was extensive and took longer than performing a full backup and then restoring to the log shipped server.
What we ended up doing was a FULL backup after the bulk load and then re initializing the log shipping database. This was a much more efficient practice for us.
Steve
October 28, 2010 at 8:21 am
thanks steve...
it is a pretty good practise. we dont have the authority to do all these things.but we can suggest this to the senior most DBA's.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply