September 10, 2009 at 8:24 am
We recently started a weekly maintenance plan on one of our databases. Steps, in order, are as follows: Check DB Integrity, Shrink DB, Reorg Index, Rebld Index, Update Statistics, Cleanup History, Backup DB - Full, Maintenance cleanup. Since implementing these tasks our log file is getting out of control, currently at 20G. When the db was originally created the autogrowth, on both data and log, was set to the default setting. We assume it is due to the Reorg and Rebuild of the indexes, but we are not quite sure the best approach to resolve the issue. We can go in and set a cap for the growth but we really have no idea what the size should be. We do not want to set the size too small because we assume that too would cause a performance issue. Any help, guidance, or best practice advice would be greatly appreciated.
September 10, 2009 at 10:17 am
What did you do for backups before setting up this maintenance plan?
I ask, because SQL Server will "decide" that your recovery model is simple until you take a backup. So if this was the first backup you've taken, and the recovery model was set to full (the default), the act of taking the backup put your log into full recovery mode. In that case, the growth you've seen is simply reflective of normal activity (unless you know there have been unusual, large jobs run), and your best bet* is to let it grow until it levels off to find out what kind of size you need.
*This isn't entirely accurate - you're always better off pre-allocating space than just trusting autogrowth, but if you've got no idea how big it will be when it stabilizes, you're pretty much stuck letting it fragment and fixing it later. See Kimberly Tripp's excellent post for more information.
(Edited to give credit to the right person for the link)
______
Twitter: @Control_Group
September 10, 2009 at 10:29 am
We recently inherited this production db. There was a weekly maintenance plan set up, but it had not been executed successfully since originally being setup in '08. However, the daily (full) backups performed successfully.
September 10, 2009 at 11:20 am
Well, so much for my first guess. 😉
With that out of the way, a few comments/questions:
1) Why are you routinely executing a DB shrink? In general, shrinking a database is a bad idea, and executing a database shrink as a part of scheduled maintenance is - I'm tempted to say never, but I'm sure there are exceptions - very rarely a good idea. Basically, it's safe to assume that the DB grew to whatever size it did because it needed the space. If you take that space away, it will only have to reclaim it later, and growing the file on disk is an expensive operation.
2) You list index reorg followed by index rebuild - are these on the same indexes? If so, you can skip the former. The rebuild effectively drops and recreates the index, so spending time (and log space) reorganizing it immediately before a rebuild doesn't really serve any purpose. If the steps affect different indexes, of course, then you're fine.
3) When are you seeing the log file growth? Does it spike on weekends, or is it growing every day?
______
Twitter: @Control_Group
September 10, 2009 at 12:56 pm
Honestly I can't tell you why the maintenace job included the shrink database. Also, we took your advice and compared the tables on the Reorg to Rebuild and they are the same. Not sure why this was setup this way as well.
After researching the history on this db we found that the log file was approximately 6.2G.
It is obvious we need to rebuild this weekly maintenance task, and of course get our log file down to an appropriate size. Can you offer any other suggestions? We appreciate your feedback.
September 10, 2009 at 2:42 pm
I'm not entirely certain what you mean when you say you researched the history, and the log file was 6.2 GB - do you mean that it's been generally around 6.2 GB in the past, but it's spiking up to 20 GB now?
Assuming that's the case, and assuming that you've seen this size increase only since you started running the maintenance plan, then it's a safe bet that it's the index maintenance causing the log to grow. If this is the case, then I would expect to see the log grow through the execution of the maintenance plan, then pretty much stay stable after that.
The thing to note - and forgive me if I'm telling you things you already know, I just think it's best to cover all the bases - is that a full backup truncates the log, but doesn't shrink it. That is, it's essentially marking a bunch of space as available for re-use, but not releasing it to the OS. Again, this is because growing a file is an expensive operation, and can lead to fragmentation (the blog post I linked earlier covers this far better than I can).
If the index rebuild really is the only thing growing the log (which should be obvious, depending on when you've seen the log file grow. If it's growing between maintenance plan executions, then it's clearly due to some other activity), one option you could explore is setting the database recovery model to BULK_LOGGED immediately before you do the index rebuild, then setting it back to FULL immediately after it finishes. This will have the effect you're looking for, in that it will minimally log the index rebuild, meaning it won't grow the log file as much.
However, be aware that when the database is set to BULK_LOGGED, you lose the ability to do a point-in-time recovery from any transaction log backup taken while the database was set to BULK_LOGGED. So my recommendation, if you want to go this route, is to:
1) Take a log backup
2) Set BULK_LOGGED
3) Run your index rebuild
4) Set FULL
5) Take a log backup
The caveat, here, is that the log backup will still be as big as it would have been without setting BULK_LOGGED, since the log backup will capture both what's in the log and all the changed extents in the database.
For more (and better) information than I can provide, see this MSDN page.
______
Twitter: @Control_Group
September 10, 2009 at 3:25 pm
We read the article by Kimberly Tripp. As suggested we ran DBCC LOGINFO on the db and it returned 536 records (VLF's).
September 11, 2009 at 12:46 pm
Ok here is our game plane.
1.) Backup the log file
2.) Set Recovery to Simple
3.) Shrink the log
4.) Set Recovery back to FULL
5.) Run a full backup
6.) Remove the Shrink Database from the maintenance plan (Even though our ERP vendor told us to turn on Auto Shrink)
7.) Remove the Reorg from the maintenance plan
8.) Run the maintenance plan and document the growth of the log file
Did we miss anything?
September 11, 2009 at 2:47 pm
Yes, bpowers you missed somethin it is called best practicies ;-))
1) you shouldnt shrink database/database files/log files ! there are no reason to do this!
2) what about dbcc checkdb ? if you dont use it - you reaaaly should!
September 11, 2009 at 3:30 pm
If I don't shrink the log how do I get my 20G log file back down to the 6G it should be? I have over 530 VLF's on this database. And yes we run checkdb in our weekly maintenance plan.
September 11, 2009 at 4:26 pm
you have a large number of VLF's because you badly mange your log files.
1) you shouldnt shrinkt it
2) log file should have "stable" size
3) 530vlf ? you are using autogrowth this is the reason...
September 12, 2009 at 7:48 am
bpowers (9/11/2009)
Ok here is our game plane.1.) Backup the log file
2.) Set Recovery to Simple
3.) Shrink the log
4.) Set Recovery back to FULL
5.) Run a full backup
6.) Remove the Shrink Database from the maintenance plan (Even though our ERP vendor told us to turn on Auto Shrink)
7.) Remove the Reorg from the maintenance plan
8.) Run the maintenance plan and document the growth of the log file
Did we miss anything?
You don't need to set the recovery model to simple - that will break the log chain and leave your database exposed until you complete the full backup. It is not necessary to do it this way.
1. Backup the log
2. Backup the log again (exactly as for step 1)
3. Issue a CHECKPOINT command
4. Shrink the log using DBCC SHRINKFILE to around 1-2GB
5. Expand the log to the maximum size you expect it to need (in 8GB steps if need be).
6. Backup the log for a third time
7. Remove the shrink and the reorganization from the maintenance plan
8. Add a consistency check to the maintenance plan
If the database is replicated, you will need to ensure that replication is up-to-date before starting.
If you can perform these steps while concurrent user activity is at a minimum, that will help.
When shrinking the log, use the output from DBCC LOGINFO to determine the exact size - you will want to eliminate as many small VLFs as possible, while retaining enough space to prevent it auto-growing due to concurrent log activity.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 12, 2009 at 12:41 pm
I did the shrink last night, pretty much in the same manner you posted. I was getting pretty frustrated; shrink, don't shrink, autougrow, don't autogrow, etc... I just went with my gut and shrank the thing. Now that I have it under contro I can take the steps to keep it that way. It was a good learning experience. I appreciate the feedback very much. And thanks even more for responding constructively rather than just telling me I did a crapy job with the log, which I inherited, and not offering any advise on how to get back on track.
It is greatly appreciated.
September 12, 2009 at 10:03 pm
No worries 🙂
I'm glad it worked out for you and you found it a positive experience overall.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 7:22 am
Ok. The weekly maintenance job executed last night without a hitch. However, due to the rebuild on the indexes the log file grew 4G. The VLF's grew from 4 to over 600 on the db. Is this normal? Should I be worried about the growth? I notice that the rebuild index option on the maintenance task does not have the option to set the rebuild at a certain fragmentation rate. How does it determine when to rebuild an index? Any help will be appreciated.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply