March 29, 2010 at 10:09 am
PLATFORM: SQL Server 2005 (Ent Edit)
For several months now I've been recording the changes in the Log file of the DB I am working on so that I can see over the average 30 day (or monthly) cycle just how the log file grows and shrinks from activity and backup operations. I now have a very good idea as to what the max size the log file will grow to during the average month.
Assuming that I have the space available to do so, can anyone think of a good reason why I (or anyone in a similar scenario) would not want to set the Log files Initial Size to the amount of drive space it is most likely going to peak at or near?
Actually I would set the Initial Size to the largest it has ever grown to and add to that the percentage I have the log file set to grow so that when it reaches that estimated peak there is a little more in the log file so that it doesn't add more space when its not yet actually needed.
All thoughts on this are appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!March 29, 2010 at 10:55 am
If you can realistically set the log to a size that is both within the scope of your system and big enough for the needs of the system, yes, do it. You don't lose anything from having it set to that size and you avoid the issues of trying to grow the log real time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2010 at 10:58 am
I agree with Grant. If you can do it, go for it. I don't see any Cons to the situation and only Pros.
Just don't make the mistake of setting Max Size as the same number as Initial Size. That WILL cause problems if the log needs to grow anyway.
March 29, 2010 at 11:13 am
Hi Brandie, since he knows the max size the log can reach, is it not ok to keep the initial size and max size the same with auto shrink disabled? Please correct me if I am wroing, but wont keeping this the same help in controlling db fragmentation?
March 29, 2010 at 11:17 am
Waseem Jaleel (3/29/2010)
Hi Brandie, since he knows the max size the log can reach, is it not ok to keep the initial size and max size the same with auto shrink disabled? Please correct me if I am wroing, but wont keeping this the same help in controlling db fragmentation?
You can turn off auto grow, but you need to be aware that if the log does fill, you'll be dealing with issues caused by a full file and the repercussions of that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2010 at 11:25 am
Wow, lots of replies and quick on this one. Thanks.
In our case the drive space is definately not an issue adn when I make this change I will leave the autogrow enabled as it is now just in the event that we have an out of the ordinary event that causes greater then normal log file growth.
My main goal was to eliminate as much auto-growth as I could and being that I've gotten several months worth of log file changes history (stored these hourly in a custom table starting several months back) I have a pretty good idea of what to expect %90 of the time if not more. So while the log file may still need to autogrow after this change, it shoudl be far less likley and much less often then it has been.
Thanks again for replying so quickly.
Kindest Regards,
Just say No to Facebook!March 29, 2010 at 11:43 am
Waseem Jaleel (3/29/2010)
since he knows the max size the log can reach, is it not ok to keep the initial size and max size the same with auto shrink disabled?
There's nothing in SQL Server that will prevent you from doing this, but as Grant said, it's not a good idea.
He can know his size limit now, but as his business grows, so too will the number of transactions coming into the db. Usually, within six to twelve months, most people see a significant change in the size of both log and data files. If Max Size is set to equal Initial Size, your db will freeze and have all sorts of unfriendly errors leading to a lot of panic by CEOs and CIOs and people who can show you the door.
Best practice is to NEVER set Max Size = Initial Size. But it's up to each individual business to decide if that's what they really want to do or not.
Please correct me if I am wroing, but wont keeping this the same help in controlling db fragmentation?
DB Fragmentation has nothing to do with the transaction log growth. It has to do with the data file growth and the way you keep your indexes. So, no, keeping the log file the same size won't help or hurt fragmentation.
You may be thinking of disk fragmentation, though. If you keep the transaction log size the same as intial creation, hard drive fragmentation is less likely to occur, especially if you're using a recently defragged disk or a brand new one. All the sectors chosen on the disk will be clumped as closely together as possible (in these cases) and that will help I/O somewhat.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply