An auto-grow setting controls how much a database file expands when it runs out of space and how large it may become. Leaving default log growth settings in place can create transaction-log fragmentation and performance problems. section 9.5
Mistake: accepting the default log growth
MarketingArchive was created with default size and growth settings. Its log began at 8 MB and could grow in large increments toward a 2 TB maximum. Repeated growth creates virtual log files (VLFs); fragmented VLFs can increase I/O and block transactions. Check the actual VLF count before leaving defaults unchanged. 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 compare a database log's actual VLF count with ideal minimum and maximum ranges. section 9.5
