December 23, 2008 at 8:27 am
I'm looking for assistance controlling the Transaction Log size growth during an index rebuild. I'm working with a 44GB database in FULL recovery mode. I backup my Transaction log once every hour and the TRN log backups are never larger than 200MB, so I set my TRN log to 1GB to allow for growth. My Maintenance Plan performs index rebuilds once a week. After an index rebuild my TRN log grows to 30GB. When I perform a dbcc sqlperf(LogSpace) the next morning, the log space used is only 13%. Which means the index rebuild grows the TRN log to 30GB and now there is 87% unused space in the TRN log. Now, I want to be able to control the growth during the index rebuild somehow. Everywhere I read I find articles stating to not shrink the log file. My thought is to create a job to switch the database to bulk-logged during the duration of the index rebuild then create a second job that switches the database back to FULL recovery mode after the rebuild is completed. Can someone give me their thoughts or suggestions to help with this?
Thanks!
December 23, 2008 at 8:53 am
The minimum amount of transaction log space required will be at least as large as the largest table in the database. This is because re-indexing rebuilds each table in one transaction.
You might be able to control the growth better by backing up the transaction log more frequently. I usually setup transaction log backups to run every 15 minutes, 24 x 7, or even more frequently if there is high activity.
December 23, 2008 at 9:13 am
When set to bulk-logged (or simple) the "alter index rebuild" operation is minimally logged so, you should see some savings in log growth during the operation. Your thought of switching recovery models is a good idea and you should see some benefits as part of that. A great post, with the sequence of how you should consider doing things is here - http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/ebb29bcd-5aa8-4015-b3c3-7d60b23009e7/
The following quote from this post is very important in your considerations for space for the log backup following the index rebuild operation;
"Also note that BULK_LOGGED will prevent the log file from growing so much during an index rebuild but the log backup will NOT be smaller. It will contain all the data extents changed by the index rebuild operation."
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2008 at 12:05 pm
Ok, I do want to create a custom script to switch the db to bulk_logged during index rebuilds. I started creating a script and if I should be posting this in the T-SQL forum just yell at me. Here is what I have so far:
STEP 1:
'take a log backup
BACKUP LOG [database] TO D:\dbbackup WITH NO_TRUNCATE;
**Question: How can I have this statement auto name the TRN backup with a date stamp?
STEP 2:
'switch to BULK_LOGGED
USE master;
ALTER DATABASE [database] SET RECOVERY BULK_LOGGED;
STEP 3:
'Reorganize data and do the index rebuilds
**What dbcc statement should I be placing in here? DBCC indexdefrag? DBCC DBREINDEX?
I basically want to just perform the optimization that the maint plans does. Right now it's set at reorganize data and index pages and change free space per page percentage to 10%. Unless I should be running some other reindex task.
STEP 4:
'switch back to FULL recovery mode
USE master;
ALTER DATABASE [database] SET RECOVERY FULL;
STEP 5:
'take a log backup
BACKUP LOG [database] TO D:\dbbackup WITH NO_TRUNCATE;
Can I get any input on this rough draft T-SQL script?
December 23, 2008 at 1:28 pm
The overall layout of things as you have them looks good. There are scripts on this site that you can search out that will give you the syntax for backups with date time stamping. Steve Jones wrote one of the first ones on here that I still like.
As for whether to use INDEXDEFRAG of DBREINDEX, that is something you have to determine based on your need. Read over the documentation in BOL and ask any specific questions you have from that.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2008 at 2:55 pm
Marker (12/23/2008)
Ok, I do want to create a custom script to switch the db to bulk_logged during index rebuilds. I started creating a script and if I should be posting this in the T-SQL forum just yell at me. Here is what I have so far:STEP 1:
'take a log backup
BACKUP LOG [database] TO D:\dbbackup WITH NO_TRUNCATE;
**Question: How can I have this statement auto name the TRN backup with a date stamp?
STEP 2:
'switch to BULK_LOGGED
USE master;
ALTER DATABASE [database] SET RECOVERY BULK_LOGGED;
STEP 3:
'Reorganize data and do the index rebuilds
**What dbcc statement should I be placing in here? DBCC indexdefrag? DBCC DBREINDEX?
I basically want to just perform the optimization that the maint plans does. Right now it's set at reorganize data and index pages and change free space per page percentage to 10%. Unless I should be running some other reindex task.
STEP 4:
'switch back to FULL recovery mode
USE master;
ALTER DATABASE [database] SET RECOVERY FULL;
STEP 5:
'take a log backup
BACKUP LOG [database] TO D:\dbbackup WITH NO_TRUNCATE;
Can I get any input on this rough draft T-SQL script?
I think doing this is a very bad idea without at least attempting to follow the suggestions that I gave you first. Changing the database recovery model for routine maintenance operations is not a good idea.
If you are going to run that script, you should run a full backup as soon as you switch back to full recovery. Otherwise, you will not be able to recover to any point after the start of the reindexing.
December 23, 2008 at 3:02 pm
I agree and the post that I referenced discusses these concerns openly.
Marker - Please make sure you read the rest of the post that I referenced to ensure that you know what risks are there for data loss by using this scenario.
Thanks Michael for stressing this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2008 at 3:18 pm
Thanks for your help. I'm thinking I'll just try increasing the TRN backup frequency during the index rebuild to see if that helps for now.
December 24, 2008 at 11:54 am
Marker - Further to DavidB's reply about DEFRAG v. REINDEX, if you are running SQL Server 2005 search Books Online for this:
Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
MSFT has provided a script that will dynamically choose which level of index optimization to run based on current fragmentation levels. I created a job using that framework and it saved me hours of R&D. I also have a similar set of objects (tables & SP's) for SQL 2000 if that's what you're running. Contact me offline if you are interested in more info.
Jim
~~ Everything in moderation, including moderation. ~~
December 24, 2008 at 12:17 pm
try adding a performance alert to kick off your trn log backup job based on percentage log space used, then as log fills (at any time) it will automatically back up and help prevent log growth.
Calculate percentage based on space for backups, size of log, how long reindex job takes and how long log backups take.
50% space used is a good starting point.
---------------------------------------------------------------------
December 24, 2008 at 12:27 pm
Wow, great ideas. JimBama i'm still using SQL 2000. Thanks for the advice though. I'm going to try and setup that alert that George suggested.
Thanks!
January 14, 2009 at 12:48 pm
Ok, I have attempted to backup the TRN file during the hours of the DB Maintenance Plan. I setup a DB Maint Plan that kicks off 15min before the Optimization to backup the TRN file every 15min. The TRN backup is completely ignored. During the DB Optimization then kicks off after the optimization completes. In the meantime, my TRN file grows from 1GB to 31GB. What gives? What are other people out there doing to resolve this issue. Man, how frustrating...
January 14, 2009 at 3:11 pm
At this point I've concluded that I am going to have to leave the TRN log at its max size. I fall under the category of an accidental DBA and analyzing particular indexes to rebuild rather then following the Maint Plan of rebuilding all indexes seems complicated to me. I'm hoping someone can shed some light on this, otherwise I'm stuck with working with a 30GB transaction log.
January 14, 2009 at 3:25 pm
Marker (1/14/2009)
The TRN backup is completely ignored. During the DB Optimization then kicks off after the optimization completes.
Just to clarify, you are actually backing up the log during the optimization process correct? A good primer on transaction log size maintenance can be found here - http://support.microsoft.com/default.aspx/kb/873235
Please note the information at the bottom of the KB article regarding rebuilding indexes and transaction log growth. Some of this is too be expected.
Another question, if the log grows that big as part of the routine maintenance can you leave it that large to support the future rebuilds? Again, having a transaction log grow is not terrible as long as that is part of normal operations and you are doing regular tran log backups.
Hope this helps a bit.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 15, 2009 at 3:31 am
any chance there is a full backup running at this time? This would prevent the log backup running.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply