November 11, 2009 at 2:34 pm
I have a 20GB database with a transaction log that averages about 9MB. I have the recovery mode set to full and I back up the transaction log every 30min. At night I run a maintenance job that checks database integrity, rebuilds the index, and then does a full backup. The backed up file size is about 15GB.
The problem I have is that the rebuild index task increase the 9MB transaction log to almost 13GB! The next tlog backup after the full backup averages about 10GB compared to the running average of 600KB of the other tlog backups. I can understand the rebuild index task causing a very large increase of the transaction log size but isn't the full backup task that runs next supposed to bring that size back down? The transaction log only decreases back to it's normal size after the first tlog backup, but the tlog backup is then 10GB. How can I keep that first tlog backup's size in check? I thought the full backup was supposed to truncate it?
Thanks in advance for any help!
November 11, 2009 at 2:40 pm
I did read about an idea that says to switch the recovery mode to bulk-logged before the rebuild and then switch back to full afterwards. Is this a good idea?
thanks!
November 11, 2009 at 2:44 pm
Full backups do NOT truncate the log, and no sort of backups shrink the file.
If your log is having to grow to 13 GB to accommodate the reindex, leave it at that size.
you may want to look into separating out the tables you reindex to different times or only reindexing those indexes that are actually fragmented beyond a certain point. There is a script in BOL under sys.dm_db_index_physical_stats to do that.
error in dmf name!
---------------------------------------------------------------------
November 11, 2009 at 2:48 pm
Thanks for the reply. How do I keep the transaction log at 13GB when the next tlog backup I do automatically shrinks it back down to about 9MB?
Sorry, I'm pretty green at this. 🙂
November 11, 2009 at 3:07 pm
Brian Fischer (11/11/2009)
I did read about an idea that says to switch the recovery mode to bulk-logged before the rebuild and then switch back to full afterwards. Is this a good idea?thanks!
yes you could do this if space for the log file is a problem or run time for the reindex is a problem.
Bear in mind you lose point in time recovery whilst the database is in bulk-logged mode, so as soon as possible after the reindex switch back to full mode and take another log backup.
In this mode the log will not grow as much but the actual log backup could be al ot larger as it will contain all extents changed. see http://msdn.microsoft.com/en-us/library/ms190692.aspx
---------------------------------------------------------------------
November 11, 2009 at 3:09 pm
Brian Fischer (11/11/2009)
Thanks for the reply. How do I keep the transaction log at 13GB when the next tlog backup I do automatically shrinks it back down to about 9MB?Sorry, I'm pretty green at this. 🙂
this should not happen. does your log backup also contain a shrink command.? If so remove it. You are fragmenting the log file, that plus the constant shrinking and regrowing affects performance.
---------------------------------------------------------------------
November 11, 2009 at 3:22 pm
A little light reading for you 🙂
http://www.sqlservercentral.com/articles/64582/
---------------------------------------------------------------------
November 11, 2009 at 3:48 pm
george sibbald-364359 (11/11/2009)
yes you could do this if space for the log file is a problem or run time for the reindex is a problem.Bear in mind you lose point in time recovery whilst the database is in bulk-logged mode, so as soon as possible after the reindex switch back to full mode and take another log backup.
In this mode the log will not grow as much but the actual log backup could be al ot larger as it will contain all extents changed. see http://msdn.microsoft.com/en-us/library/ms190692.aspx
There is typically no activity in the wee morning hours and the full backup task occurs right after the reindex. And since there is a full backup, will the log backup still be large or will all the changes have been backed up by the full backup?
PS - I'm also testing these things on a development server to see if I can answer my own questions. 🙂
November 11, 2009 at 4:32 pm
A full backup does not truncate the transaction log. This is only done by a backup log (transaction log backup). Also, please understand that a log truncation is NOT the same as shrinking the log file, which is something you don't want to do on a regular basis.
November 12, 2009 at 4:25 am
[
There is typically no activity in the wee morning hours and the full backup task occurs right after the reindex. And since there is a full backup, will the log backup still be large or will all the changes have been backed up by the full backup?
the log backup will still be large despite the full backup. This is because the full backup does not truncate the log. the full backup will put a marker in the log signifying it was done so if you do a restore starting from that full backup the following log restore knows where to start.
The log shrink may well be incorporated in that full backup job. wherever it is remove it.
If you have not done so already it is well worthwhile reading that article.
---------------------------------------------------------------------
November 12, 2009 at 1:15 pm
OK, so I must have been smoking something. The log file does NOT shrink when I run my maintenance jobs. Not sure what I saw earlier, but I am not shrinking it.
Thanks for helping me understand, but just so I get it right:
- The tranlog increases because of the index rebuild.
- What ever the tranlog increase to, I should leave it alone (in my case about 14GB). NO SHRINKING!
- The first transaction log after a full backup will always be big because of the index rebuild. The full backup does not truncate it.
November 12, 2009 at 1:16 pm
So seeing as how the database activity is next to nil in the early morning hours. Would it be alright to take the following steps?
- set the recovery mode to bulk-logged
- rebuild index
- set the recovery mode to full
- do a full backup
Performing a full backup last would start a new log chain anyway right?
November 12, 2009 at 2:16 pm
Brian Fischer (11/12/2009)
OK, so I must have been smoking something. The log file does NOT shrink when I run my maintenance jobs. Not sure what I saw earlier, but I am not shrinking it.Thanks for helping me understand, but just so I get it right:
- The tranlog increases because of the index rebuild.
- What ever the tranlog increase to, I should leave it alone (in my case about 14GB). NO SHRINKING!
- The first transaction log after a full backup will always be big because of the index rebuild. The full backup does not truncate it.
you got it
---------------------------------------------------------------------
November 12, 2009 at 2:22 pm
Brian Fischer (11/12/2009)
So seeing as how the database activity is next to nil in the early morning hours. Would it be alright to take the following steps?- set the recovery mode to bulk-logged
- rebuild index
- set the recovery mode to full
- do a full backup
Performing a full backup last would start a new log chain anyway right?
this would be slightly better as the log chain is not broken by going to bulk-logged, you just lose point in time recovery for log backups with a bulk logged operation in them.
- take a log backup
- set the recovery mode to bulk-logged
- rebuild index
- set the recovery mode to full
- do a log backup
---------------------------------------------------------------------
November 12, 2009 at 2:27 pm
george sibbald-364359 (11/12/2009)
this would be slightly better as the log chain is not broken by going to bulk-logged, you just lose point in time recovery for log backups with a bulk logged operation in them.- take a log backup
- set the recovery mode to bulk-logged
- rebuild index
- set the recovery mode to full
- do a log backup
How would I work this into my nightly maintenance plan with a full backup then. Just to give a background this is my current plan:
- Check database integrity
- Rebuild indexes
- Full backup.
Thanks!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply