March 26, 2010 at 10:55 am
Hello All,
I'm new to log shipping and am having problems with log file size.
I have log shipping set up on our primary production server (SQL1) shipping to our failover server (SQL2). We have index REORGS scheduled every Wed and Sun at 01:00. When this runs, it makes the log file around 40GB on SQL1. That's fine -- we have plenty of space.
The log file on SQL2 is also 40 GB and there is the problem. We down to a couple GB free and there is no possibility of buying additional disk drives. I've practically begged my manager but they start talking about "budget".
So -- while I know I shouldn't shrink log files on a regular basis, my manager insists that I do it. What is the safest way to shrink the log file on SQL2 after the index rebuild/reorg jobs? I want to make sure I don't break the log chain, etc. Our log shipping is scheduled to run from 06:00 to 20:00 during the week. It does not run on weekends. Also, if I shrink the log file on SQL1 will that propogate to SQL2? I don't think it does but it seems like I read that somewhere.
I'm working on the indexing jobs to only rebuild/reorg what is fragmented but I haven't gotten that done yet. The shrink file is (hopefully) a temporary solution.
Please let me know if you need more information...
Thanks,
Brett
March 26, 2010 at 3:44 pm
First, it's unadvisable to shrink the log file if it grows to that size a couple of times a week. You take a major performance hit when the log file grows. It grows to that size because it needs it.
What is your current frequency of log backups?
Probably the easiest solution would be to increase the frequency of backups (every 15 minutes, every 5 minutes, whatever). Then reduce the retention period on the secondary server (for instance keep only 30 minutes of logs on the secondary).
But, this would not fix your problem entirely, since you are not constantly shipping, you are copying over two days of logs on Monday morning (so it really does not matter the frequency). To be honest, I've never heard of log shipping for DR that runs only during business hours. Is there a business reason that you don't ship nights/weekends?
Also you may want to consider an index defrag then occasionally do a reindex.
Finally, what level of fragmentation do you have before you run the re-index. You may find that you don't need to do it near that often (at several of my jobs we did (and currently) do manual rebuilds when required and when it's a proper time based on business needs)
-Greg
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
March 26, 2010 at 5:55 pm
brett.walker (3/26/2010)
As gregory says - run your log backups at the weekend as well to capture this activity in smaller chunks
Also, if I shrink the log file on SQL1 will that propogate to SQL2? I don't think it does but it seems like I read that somewhere.
A shrink on the primary will propogate to the secondary but its a waste of time if it is just going to grow again.
I'm working on the indexing jobs to only rebuild/reorg what is fragmented but I haven't gotten that done yet.
theres plenty of scripts on the WEB and trhis site under scripts for this, there is even one in BOL, 2 minute job to get this set up, so make it a priority.
---------------------------------------------------------------------
March 26, 2010 at 9:14 pm
brett.walker (3/26/2010)
I'm working on the indexing jobs to only rebuild/reorg what is fragmented but I haven't gotten that done yet. The shrink file is (hopefully) a temporary solution.
Hey Brett,
Check out Michelle Ufford's popular log script here: http://sqlfool.com/2009/06/index-defrag-script-v30/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 8:34 am
Hi all,
I understand about not shrinking because it's just going to grow again. I've repeatedly advised my manager that we need more disk capacity but they simply will not do it. Budget, bla bla bla...
We currently ship every 15 minutes. The log shipping itself is not what's causing the problem. It's an index reorg that runs Wed and Sun at 1:00 AM. I'm working on reducing the number of indexes we reorg, etc.
We don't log ship during the weekends because there are no changes being made. We are a M-F 8-5 shop.
My question is, if I can't reduce the number of indexes we rebuild/reorg (depending on frag), I need to (by manager directive) shrink the log file after every rebuild/reorg. I have explained the danger but he insists we do it his way...
I have considered rebuilding/reorging maybe 25 (or whatever) indexes and then do a shrink, do 25 more, do a shrink -- all as part of the index maintenance job. I realize this is ridiculous but I don't know what else to do given the artificial restrictions I've got.
Again, I have repeatedly stressed to him the dangers but for some reason he won't hear it...
Thanks for any advice, suggestions, etc.
Brett
March 29, 2010 at 8:37 am
I forgot one question.
I may need to (again by manager directive), shrink the log file during the day when log shipping is running. We are at 98+ % full on the log drive on the destination log shipping server. How do I go about shrinking the log file on the destination server when log shipping is active? I don't want to break log shipping but I may have no choice but to shrink...
Thanks again,
Brett
March 29, 2010 at 9:25 am
shrinks don't break logshipping. Will hurt your performance though,
You are making changes at the weekend - the reindexing. that as you know causes log activity. At the moment you capture that in your first backup monday, if you log ship at the weekends as well you have a chance there to minimise log growth (presuming reindex takes longer than 15 mins?)
---------------------------------------------------------------------
March 29, 2010 at 9:30 am
So, if I shrink the log file on the primary server, the shrink will propogate to the destination server? Right now, my critical issue is freeing up space on the destination server log drive.
Thanks,
Brett
March 29, 2010 at 9:46 am
brett.walker (3/29/2010)
So, if I shrink the log file on the primary server, the shrink will propogate to the destination server? Right now, my critical issue is freeing up space on the destination server log drive.Thanks,
Brett
yep, it will.
---------------------------------------------------------------------
March 29, 2010 at 12:27 pm
My suggestion would be to scatter you index rebuilds out throughout the week, in the hours having the lowest traffic times (usually in the wee hours of the morning.)
Consider your 15 minute log interval... each day try break them up at the 15 minute intervals such that your log will not grow so large at any one time.
Beyond that there is nothing you can do... the index rebuilds and the shrinks will probogate.
The probability of survival is inversely proportional to the angle of arrival.
March 29, 2010 at 12:43 pm
Thanks to everyone for their advice, etc. It has confirmed some things for me and I learned some things. I think I'm good to go (at least for now).
Thanks,
Brett
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply