November 11, 2008 at 5:02 pm
Gila,
I see what you are getting at. When I get the answer to what your asking, I'll be able to better judge what to set my Transaction Log size at.
Thanks.
November 11, 2008 at 5:18 pm
Bingo. Then add a little bit more for cushion. If you determine that the log should be 5 GB, make it 6 or 7 GB to add some cushion for extra work that may occur.
November 24, 2008 at 8:29 am
I having trouble with my transaction log growth again. My maintenance plan backups up the TRN log every hour. During the busiest time of the day the hourly TRN log backup largest backup is 80MB. I'm having trouble understanding why every Sunday morning the TRN log grows to 30GB. It looks like the database integrity check is the cause of this. The database integrity check runs every Sunday at 12am. Every Sunday between 12am and 3am the TRN log grows from 2MB to 30GB. In the DB maintenance plan I have the following set: Check the database integrity, Include indexes and attempt to repair any minor problems. How can I control this?
November 24, 2008 at 8:45 am
I just noticed that maint plan also performs an optimization at the same time. It is set as follows:
Reorganize data and index pages.
Change free space per page percentage to 10%
This could also be the culprit. What is setup wrong?
November 25, 2008 at 9:23 am
Index rebuilds are logged operations and hence will cause tran log growth. There's nothing wrong.
You can switch to bulk-logged for the duration of the rebuild to reduce the impact on the log.
Make sure you are NOT shrinking the database or log on a regular basis.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply