Link to home
Create AccountLog in
Avatar of jpletcher1
jpletcher1Flag for United States of America

asked on

SQL 2005 Reindex and Large Transaction Log

Every other Sunday I run a maintenance plan on a 17GB database that does the following:
1.  Backup database
2.  Change recovery model to bulk logged
3.  Reorganize Index
4.  Rebuild Index
5.  Update Statistics
6.  Backup database
7.  Change recovery model back to full

It appears that when it does the rebuild index step the transaction log file fills the drive it is on (over 20GB).  Then the only way I can find to shrink it is to:

1.  Shrink the tlog file
2.  Backup the tlogs
3.  Shrink the tlog file again

I'm wondering what I can do to prevent this.  I've seen similar posts, but my maintenance plan is a little different than the others, so thought I would post.  
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

When was the last time you did a Transaction Log backup?
Never mind I missed the second part.  You need to stop doing SHRINKFILE.  This is a very bad idea.
Try it this way:
1. Full database Backup
2. Transaction Log Backup
3. Rebuild Index
4. Transaction Log Backup
5. Update Statistics
Avatar of jpletcher1

ASKER

I can try that, but it seems like the tlogs are fine until the rebuild index, then they fill up the drive and at that point a simple tlog backup doesn't clear it without also using the shrink part.  Any comments?
>>I can try that, but it seems like the tlogs are fine until the rebuild index, then they fill up the drive<<
Then you may need to:
A. Increase the size of the Transaction Log or
B. Reindex a few tables at a time and run a Transaction Log backup in between.

>>at that point a simple tlog backup doesn't clear it without also using the shrink part. <<
The Transaction Log Backup will clear it, but it does not shrink the file.  Nor should it and nor should you.  By repeatedly shrinking the Transaction Log all you have accomplished is fragment the file beyond recognition.  But don't take my word for it, run the following:
USE YourDatabaseNameGoesHere
DBCC LOGINFO(YourDatabaseNameGoesHere)

I suspect you have close to 400 entries, when you should have around 50.
So what you're saying is that I should let my logfile be large all the time then (even though the file is mostly empty most of the time)?  If that is the case, and we do tlog backups every couple hours, will every tlog backup be the full file size or only the size of the data that is in large tlog?  Does that make sense to ask?

Thanks for the help
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok I understand better now.

When you say the last part, "Incidentally, you may want to question the wisdom of running a re-index every night.", do you mean we should run the re-index more often or were you under the impression that we ran it every night?  We only run it every other weekend now.

Lastly, there were 286 entries when I ran the query you suggested.  Is there something I should do to defrag the transacation log?  Then I will allocate more disk space to the tlog partition and be sure the log file is never shrunk from now on.
>>We only run it every other weekend now.<<
I would only run it when necessary.  If this is a well-designed database you can go months without having to re-index.

>> Is there something I should do to defrag the transacation log?<<
Read this article:
8 Steps to better Transaction Log throughput
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Some more reading material:
Why you should not shrink your data files
http://sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
I am not a DBA obvioulsly, but I will always now remember not to shrink database files.  That was just the solution that was passed down to me from a previous person that once took care of this. :)  

I will give the log file partition more space and allow the log file to stay however large it does get during the it's most transaction oriented process.  I'm not sure if I'm brave enough to go through with the article to defrag though.

Thanks for all your help.  I learned quite a bit here.
One other thing I meant to ask.  In my maintenance plan I change to bulk logged before I run the index reorgs and rebuilds, then back to full after.  Should that not reduce the logging that happens during the index tasks?
Apparently using bulk-logged Recovery Model does in fact reduce the amount logged.  I would not do it, but if you do make sure that:
A. There is no activity at that time.
B. Make sure that you do a Full-backup after you change back.
Yes, I do backups before and after.  I've read the bulk logged does reduce the rebuilds, but not the reorgs.  Confusing for a non-dba guy like me.
Personally, choosing between having to do two full backups and not switching to Bulk-logged I would go with the second option.  But you need to do what is comfortable for you.
So ultimately what would the best plan be?

1.  Backup the database file
2.  Do reorg/rebuild of indexes
3.  Backup the database file

Is that what you are suggesting?
No.  I would not change the Recovery Model.  I would do a single Full backup.

And I would not reindex all the database, just the tables that needed them.  If that uses a lot of Transaction Log disk space, than I would do more frequent Transaction Log backups while it is running it.

But you need to do what is comfortable for you.  Backups are a critical function, get them wrong and you may as well put your head between your legs and kiss your ass good bye.
I didn't include the recovery model change in my last post.  Just backup DB, do maint work, and backup DB when done again.  I found a script that will only reindex/org selective indexes here:

http://www.sqlmusings.com/2009/03/15/a-more-effective-selective-index-rebuildreorganize-strategy/
>>I found a script that will only reindex/org selective indexes here:<<
Yep.  That is the correct way to go.