A log block is a container for transaction log records and the unit in which SQL Server flushes those records to disk. Log records capture DML and DDL activity; in Full recovery model, they also record allocated and deallocated pages and extents. section 9.5
Mistake: letting the log grow in small default increments. Growth creates virtual log files (VLFs), each containing log blocks; too many VLFs fragment the log and can hurt performance. Log truncation releases inactive VLFs, not the physical log file. section 9.5 section 9.12
Keep these operational rules in view:
- Remember that an LSN identifies a log record through the VLF sequence number, log block ID, and log record ID. section 9.5
- Expect the log to cycle back to the first truncated VLF after the final VLF becomes full. section 9.5
- Investigate active VLFs retained by incomplete transactions, availability-group replicas, or change data capture before assuming truncation is failing. section 9.5
Check for log fragmentation
Run the supplied DMV query against MarketingArchive to compare actual VLF counts with minimum and maximum ideal counts derived from the log-file size. Use the result to identify growth-related fragmentation before changing the growth strategy.
