There are some myths widely spread about transaction log that are to be debunked here:
- 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).