September 13, 2014 at 7:56 pm
I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
The standard seems to be to shrink logs after each part of the maintenance run but that feels a bit dodgy to me.
September 15, 2014 at 3:36 am
The standard seems to be to shrink logs after each part of the maintenance run but that feels a bit dodgy to me.
Nope, absolutely fine. Just don't shrink them to 1MB 🙂
If you've a good idea how large the log files need to be, it might even be wise to grow them at the start of the maintenance window.
You might be able to reduce log file usage by switching to bulk-logged recovery model for the duration of the maintenance run (if you're using full), just be aware you might lose the ability to do point-in-time recovery while in that model.
If you're using the simple model try running CHECKPOINT between large index operations.
Other tricks:
Only rebuild/reorg indexes that need it.
Avoid using ALTER INDEX ALL, seems to hold more log space.
September 15, 2014 at 3:50 am
codykonior (9/13/2014)
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario?
~1.5*the size of the largest index in the database, assuming that log backups are run after each index rebuild. If not, then ~1.5*the size of all indexes rebuilt between two log backups. That's very much a thumbsuck though.
If the risk is acceptable, consider switching to bulk-logged recovery during maintenance. Also make sure you're not just blanket rebuilding every single index in the DB. If you're using maintenance plans, stop. Use Ola's solution instead.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
The standard seems to be to shrink logs after each part of the maintenance run but that feels a bit dodgy to me.
Yes, very dodgy.
Log shrinking when you don't expect the log to need to be that size again is fine. Log shrinking when you know that the log will need to be that size again in a week is just a waste of time and resources.
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
September 15, 2014 at 6:33 am
GilaMonster (9/15/2014)
If you're using maintenance plans, stop. Use Ola's solution instead.
Yes we're using Ola - now. I'm in the process of tearing out MPs (for index maintenance anyway) from 200+ servers.
We use reorganisation everywhere because nobody ever thought twice of defining proper maintenance windows with each database when they were set up. With thousands of databases and the owners unavailable, there's no hope of ever pinning them down to get it sorted now.
I think I'm going to write an anonymised eBook on some of the stuff I'm seeing. It should make for good scary stories for DBAs to tell their children.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply