August 1, 2011 at 12:18 pm
we have a problem of a huge 90GB log file after the Sunday reorg.
Funny enough there was a shrink log job, but I found the a log backup also starting at same time.
So, now there is a message that all logical log files are in use, so it cannot shrink the log file.
(The issue we have is space. If we don't shrink, after reorg, we end up with a big log file, which in turn breaks Log Shipping)
My question is will changing the log backup time to 'after the shrink operation' help in shrinking the log? (because this is the maintenance window where we are doing all this maintenance, should be no other connections)?
August 1, 2011 at 12:44 pm
frankie11 (8/1/2011)
(The issue we have is space. If we don't shrink, after reorg, we end up with a big log file, which in turn breaks Log Shipping)
A large log file alone won't break log shipping. A large log backup might (if the network can't cope) and reorganise operations are fully logged. Shrinks are also logged, so a shrink will make your log backup bigger (it contains the reorg and the shrink log records) and makes the breaking of the log shipping more likely, not less.
If the shrink fails because 'all logical log files are in use' it means that there are no unused portions of the log to remove. In full/bulk-logged recovery it takes a log backup to make portions of the log reusable. Hence moving the log backup after the shrink will pretty much guarantee that the log won't be able to shrink because 'all logical log files are in use'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2011 at 12:50 pm
You have to send that reorg operation to the log shipping secondary or you break log shipping.
You also will need this space next Sun, so I'm not sure if shrinking after the operation actually helps you. The space is required again, so you can't use it for another purpose permanently.
You might think about a more intelligent reorg instead. Michelle Ufford has a good script here this might help: http://sqlfool.com/2011/06/index-defrag-script-v4-1/
August 2, 2011 at 1:32 pm
Thank You Gail and Steve. I realize I mentioned breaks log shipping. I’m sorry, this is not always the case.
This is what it is - the size of the log file after reorg and the lack of space on secondary requiring our intervention on a fairly regular basis, including reinitializing log shipping. Yesterday, we ran out of space on secondary for writing backups, the most recent available on secondary was already restored. Newer log backups didn't have enough space to be written (its directly written to secondary), and the backup job was failing. So I removed backups as they were restored. That fixed the problem.
Gail, I guess I confused myself here. I am splitting your response to 3 statements. Kindly help.
Statement 1 – “shrink fails…. and there are no unused portions of log to remove.”
Statement 2 – “In full/bulk-logged recovery it takes a log backup to make portions of the log reusable”. - Will a log backup before shrink make portions of log reusable, thereby a possibility of a successful shrink?
Statement 3 – “hence moving log backup after the shrink will guarantee log won’t shrink”..
So, put together - a log backup before the shrink, and then again log backups as usual after the shrink… Would that help and was that what was implied by you? If not, is my conclusion pointless?
I understand that the shrink may not be a good idea. (Previously, I was thinking the log file is so big just because the shrink failed)
Should I do away with the shrink job?
Is asking for more space allowing for future growth the best option or is shrink a viable compromise, because the biggest problem so far is only after the reorg? Also, I am planning to implement the script by Michelle Ufford. Thank You.
August 2, 2011 at 1:41 pm
frankie11 (8/2/2011)
Probably, however...
Should I do away with the shrink job?
Yes. Absolutely.
Is asking for more space allowing for future growth the best option or is shrink a viable compromise, because the biggest problem so far is only after the reorg?
The log should be big enough for all normal operations on a database, and a rebuild/reorg is a normal operation. You are hurting your performance by shrinking the log (it'll just grow again) and you may well be causing internal log fragmentation.
Shrinks are indicated for once-off usage after unusual operations have caused the data/log file to grow well beyond what is required for normal operations or after a large data archive has left the data file with free space it is not likely to reuse in a reasonable amount of time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2011 at 1:43 pm
Bottom line is this. Don't use MP to do index maintenance. Steve pointed to an awesome, extremelly efficient way to do it.
Check out my full results with it here : http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx
Once you take care of that, you should eliminate the rest of the problems.
P.S. Delete the shrink step from the maintenance plan. It shouldn't even exists in there and will be removed in Denali.
August 2, 2011 at 2:39 pm
Ninja's_RGR'us (8/2/2011)
Bottom line is this. Don't use MP to do index maintenance. Steve pointed to an awesome, extremelly efficient way to do it.Check out my full results with it here : http://www.sqlservercentral.com/Forums/Topic1126147-146-1.aspx
Once you take care of that, you should eliminate the rest of the problems.
P.S. Delete the shrink step from the maintenance plan. It shouldn't even exists in there and will be removed in Denali.
While I agree with not using the maintenance plan rebuild task, I will say that you can still use maintenance plans.
Instead of using the built-in rebuild/reorganize task - use the Execute SQL Task and call out to a custom script to rebuild/reorganize the indexes.
This allows you to still set all of the same dependencies, use the default tasks where appropriate (e.g. integrity checks, backups, etc...) and still use custom code when needed.
As for shrinking - remove it from any automated scripts, whether agent jobs or maintenance plans. It is really causing more problems than it is actually fixing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 2, 2011 at 3:38 pm
I tend to agree with Jeffrey. Nothing wrong with maintenance plans in and of themselves, but some of the tasks are not very efficient.
I'd still put in requests for most space on the secondary server. At some point the lack of space will cause other issues if you continue as things are.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply