February 11, 2004 at 8:34 am
Hi,
What would be the performance tuning gains at truncating/shrinking T-logs ... or is it solely for disk management ie: reclaiming disk space?
THanks. jeff
Many thanks. Jeff
February 11, 2004 at 9:03 am
When a file grows with large chunks, the system slows down (even appears to stop responding in some cases) for a few seconds.
If your transaction log grows to gigabytes every day, I'd consider leaving the standard transaction log size to the average size at the end of the day. The log file doesn't need to grow to fulfill standard database needs and you'll need the space anyway.
To avoid too much fragmenting in the log (making your spindle read heads waggle like the tail of a happy dog), consider "move pages to beginning of file" after transaction log backup.
When this is exceptional (such when inserting a column in a large table and populating it, shrink the log.
February 12, 2004 at 8:58 am
From a development perspective, we've found that when our large upgrade scripts are run against a database the log file grows to sizes 5 to 10 times normal operating size. This is due to the fact that upgrades often require (multiple) table-wide operations. Under these circumstances, we've found noticealbe speed increases in the upgrade batch by truncating the log and then shrinking the physical log file.
Now that we're doing this, though, I've been thinking that it's going to hurt everyday performance due to the fact that the log is going to have to regrow to normal operating size, and will likely become fragmented when it does. So I've been working on a simple matrix that maps an acceptable minimum log file size to a certain database size, so that our shrinking it during upgrades will only reset it back to this minimum, rather than the 1024KB absolute minimum when there isn't a minimum set (which is the case with the majority of our customers). I think this will allow our upgrade shrink to help upgrade performance, but not at the cost of everyday performance later.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply