A transaction log file records database activity for transaction processing and recovery. Log records capture executed DML and DDL statements; in the Full recovery model, allocated and deallocated pages and extents are logged as well. Each record has a log sequence number (LSN) made from a VLF sequence number, log block ID, and log record ID. section 9.5
Mistake: treating a huge DELETE as one harmless statement. A statement runs in an autocommit transaction unless an explicit transaction changes that boundary. A large open transaction prevents log truncation, even under the SIMPLE recovery model, so the log can fill, the query can fail with error 9002, and rollback can add further delay. section 5.8
Delete a large table in batches
Delete a fixed number of rows per statement rather than deleting every row at once. Separate statements create separate transactions, allowing the log to truncate between executions in SIMPLE recovery. Adjust the batch size for the environment; use this looping approach as an ad hoc operational script rather than deployed application code. section 5.8
Listing 5.19 Deleting rows in batches
DECLARE @RowCounter BIGINT ; SET @RowCounter = 1 ; WHILE @RowCounter > 0 BEGIN DELETE TOP(250000) FROM dbo.VeryLargeTable ; SET @RowCounter = (SELECT COUNT(*) FROM dbo.VeryLargeTable) ; PRINT @RowCounter ; END
This batch-delete script uses DELETE TOP(250000), checks the remaining row count, and repeats until no rows remain. section 5.8
A log file contains virtual log files (VLFs), and each VLF contains log blocks that hold log records. Truncation frees eligible VLFs rather than shrinking the physical log file. Frequent growth can create too many VLFs—log fragmentation—which can hurt performance. Avoid relying on autogrow, avoid multiple log files, and plan log capacity and growth increments. section 9.5 section 9.12
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 ;
This query counts VLFs for MarketingArchive and compares the count with size-based minimum and maximum ideal ranges. section 9.5
