Mistake: relying on autogrowth to manage database size. SQL Server grows files automatically when space runs out, but default 64 MB increments can turn ordinary growth into repeated delays, blocking, and resource consumption. section 9.3
MagicChoc MarketingArchive
The database grows about 5 GB daily during ETL. With 64 MB growth increments, data and log files repeatedly expand; growth consumes disk and processor resources, blocks transactions writing to the log, and may fragment files. Plan capacity instead of treating autogrowth as the sizing strategy. section 9.3
Use these safer rules:
- Pre-size files and choose growth increments appropriate to expected growth rather than accepting the defaults. section 9.3
- Give log files especially careful sizing: growth can block concurrent log writers and create transaction-log fragmentation through excessive VLFs. section 9.12
- Monitor growth patterns and capacity so new storage is available before an outage or unplanned expenditure. section 9.12
Listing 9.9 Changing the autogrowth increment
ALTER DATABASE MarketingArchive MODIFY FILE ( NAME = 'MarketingArchive', FILEGROWTH = 5GB ) ;
Apply a larger planned increment to the MarketingArchive data file with ALTER DATABASE rather than repeated 64 MB expansions. section 9.3
