SQL Server Interview Question #268
Why should transaction log files be pre-sized appropriately?
SQL Server Internals & Administration Senior Advanced
Detailed Explanation
If the log repeatedly grows during normal workload, SQL Server must perform autogrowth operations and create additional VLFs. Growth events can introduce latency and lead to an undesirable log layout.
The log should be sized for normal workload peaks, index maintenance, bulk operations, and recovery requirements, with a sensible fixed-size growth increment.
Routine shrinking followed by regrowth is usually counterproductive. Fix the cause of excessive growth and then establish an appropriate steady-state size.