Transaction log myths

There are some myths widely spread about transaction log that are to be debunked here:

• Log backup copies log file

FALSE. Someone might think if you have 100GB log, that log backup will also be 100GB, since they think the log backup is a “copy” of the log file. But that is not true. Log backup copies only a PART of the log file, from log write point of last log backup till current log write point within the log file. That means, if you have huge 100GB log file, you can have tiny log backups of eg 1MB each if you do log backup often eg. every 1 minute. If you have 10 log backups every 1 minute, the sum of that 10 file sizes will be the same as a single file you would get by backing up log after 10 minutes. It is like 10 minutes of records that can be in single file or sliced to 10 files, but the sum is the same.

• Full/diff backup will clear the transaction log

FALSE. Only transaction log backup in full and bulk_logged recovery model, or checkpoint in simple recovery model will trigger the log clearing process. Full and diff backups will only defer it until the backup finishes. If you only do a full/diff backup in full recovery model, your log will grow until the disk is full.

• Log backup cannot be taken if FULL/DIFF backup is in progress.

FALSE. Since SQL 2005, log backups can be taken concurrently with full/diff backups. Only log clearing (marking VLFs free) will be delayed until full/diff backup finishes, so your log file might grow bigger during full/diff backup. See Paul Randal’s post about that.

• Log truncate will make it smaller

FALSE. Log truncate just scans active VLFs (virtual log files) and those that are not needed anymore by anyone marks as inactive. That does not change the log file size.

• Log shrink will make it smaller

It depends. DBCC SHRINKFILE on the log can only chop-off part of the log file after the last active VLF. If the last VLF in file is active, file shrink will not make the file any smaller.

• Instant file initialization will speed-up log file allocations (growth, initialization)

FALSE. Instant file initialization only works for data files, not for log files. Log files must be zeroed-out because of recovery process. It helps the recovery process after a crash to find where the log records really ends: when it bumps into zeros or alternate 64/128 value.

• Multiple log files will make performance gains

FALSE. Transaction log file is always written sequentially, in a single thread. Multiple files won’t parallelize log writing. However, you could use additional files if for example, you run out of space on current log disk to add a new log file on the other disk.

• My database is in simple recovery model, so the log will not grow

FALSE. Transaction log is used in every recovery model, including simple. It will just be cleared more often (on each checkpoint, instead on each log backup) and some operations will be bulk-logged. It is important to properly size the transaction log even for databases in simple recovery model.

• It is a good guideline to determine the log size as percentage of database size

FALSE. Log size is determined by the maximum amount of log generated between two log clearings. For example, small database that does lot of data changes (e,g, in-place updates) can generate 100x more log than database size. Log must also fit the biggest transaction, e.g. biggest table rebuild.

• Regular log shrinking is a good maintenance practice

FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game. You could shrink after some extraordinary event that blew-up log size, but never shrink to a size smaller than is normal operational size for that database.

• TRUNCATE TABLE is not logged

FALSE. Extents (and up to 8 pages in mixed extents, per table/index/partition) are deallocated from the table and that is logged. But it generates far less log than DELETE command.

• Minimally logged operation can only happen in BULK_LOGGED recovery model

FALSE. It can occur in SIMPLE mode also.

• Every change is logged, regardless of recovery model

TRUE with one exception. The only operation that is not logged is on one part of tempdb database called row version store, used for row versioning.

• Log records are written to disk immediately

FALSE. There is a 60KB log cache in the RAM that log records are written to, not the disk. That cache is written to the disk, to the transaction log file, as soon as all 60KB is filled, any session of that database issues a commit (a real commit, not the nested one that just decreases @@TRANCOUNT), and on some other events. All log records flushed together to the disk will have the same block offset (LSN consists of VLF number, block offset, and log record number). Therefore, log block cannot be larger than 60KB (cache’s size).