Mistake #47: allowing a transaction log to grow with default settings. Each log file contains virtual log files (VLFs), and repeated growth can create too many of them. That condition is transaction log fragmentation, which can contribute to performance issues and increased resource utilization. section 9.5
The problem is easy to miss because truncating a log does not remove the log file; it truncates VLFs that contain no active transactions. A safer practice is to size the log deliberately, avoid relying on autogrow for database management, and let a required shrink be followed by growth in larger increments. section 9.12 section 9.5
The MarketingArchive mistake
MarketingArchive was created with an 8 MB log and default growth settings. Although the configured maximum was 2 TB, repeated growth produced log fragmentation instead of controlled capacity. The result can include performance problems and additional I/O during growth, with other transactions potentially blocked. section 9.5
Listing 9.11 Checking for log fragmentation
SELECT
li.database_id
, li.ActualVLFs
, mf.size/1024/1024 AS SizeInGB
, mf.size/1024/1024*2. AS MinIdealVLFs
, mf.size/1024/1024*4. AS MaxIdealVLFs
FROM (
SELECT database_id, COUNT(*) AS ActualVLFs
FROM sys.dm_db_log_info(DB_ID('MarketingArchive'))
GROUP BY database_id
) li
INNER JOIN sys.master_files mf
ON li.database_id = mf.database_id
WHERE mf.type = 1 ;
Use the supplied query to count ActualVLFs for MarketingArchive and compare that count with minimum and maximum ideal VLF estimates derived from the log size. section 9.5
