August 13, 2008 at 9:37 am
I have a 9.5 gb ldf file. We had a maint plan run every sunday at 2am and the backup trn file around 8 or 9 gb as well. I did a shrink I created another maint plan to backup the tlog every day at 10pm in order to keep the ldf small and it does until the Sunday maint plan. I read that doing a shrink every week is not good so I removed it from our Maint plan on Sunday. The ldf file goes from a few hundred mb to over 8gb after the maint plan is run. Nothing is being run database wise at that time.
We have a nigthly Tivoli backup and this Weekly Maint plan. Are these tasks in the maint plan necessary? Is there something in the Maint plan causing this?
Here is my current Maint plan tasks.
Check Database Integrity
Reorganize Index
Rebuild Index
Update Statistics
Clean Up History
Back Up Database (Full)
Maintenance Cleanup Task
August 13, 2008 at 11:57 am
Keith,
Yes, the Rebuild Index is probably what's causing the transaction log to grow. That rebuilds all indexes and index rebuilds are logged operations.
Something you might want to consider is a selective rebuild of the indexes rather than rebuilding all. There are a few scripts on this site that will check fragmentation and page count, then do an index rebuild based on that.
August 13, 2008 at 12:51 pm
A blanket rebuild isn't necessary for most tables, but it is easier to set up and I've typically run it. 8-9GB isn't a lot of space. Can you not spare that much space and leave the logs at that size? Doesn't hurt anything.
If not, I'd selectively rebuild as things reach some level of fragmentation.
August 13, 2008 at 12:58 pm
I created the lun and sized it too small. I can migrated the lun to a bigger lun. Just trying to find the best practice on maint plans and what was causing the log to grow. I'll do some research on the rebuild index. I used the Maint plan wizard to set these up and took most of the defaults.
Should I be rebuilding the indexes every week? Should I add the Shrink task back to the plan as well?
Thanks for everyones input!!!!
August 13, 2008 at 2:06 pm
Keith Knox (8/13/2008)
Check Database IntegrityReorganize Index
Rebuild Index
Update Statistics
Clean Up History
Back Up Database (Full)
Maintenance Cleanup Task
You do not need to reorganize and rebuild your indexes. Pick one of them - I would recommend rebuilding the indexes if you have the time during your maintenance window.
If you decide to rebuild the indexes, then using the Update Statistics task is overkill since rebuilding indexes updates the statistics. You can change that step to an Execute T-SQL Statement task and execute the following:
Use database; Execute sp_updatestats @resample = 'RESAMPLE';
The above command will only update statistics that need to be updated and updates the statistics based upon the sampling rate used the last time the statistics were updated.
If you decide to reorganize the indexes instead - then you need to perform the full Update Statistics task (with full scan).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply