SQL Server Recovery Models & Log Truncation
We get a fair number of questions from SQL Backup Master users about transaction log files, often accompanied by concerns regarding truncation and/or shrinking of log files.
We’ll start with the summary. It isn’t necessary to manually truncate SQL Server transaction log files. However, there may be an occasional need to manually shrink them.
We’ll talk about log truncation first. Truncation is a process that frees up space in the log file for reuse. It will occur automatically at regular checkpoints if your SQL Server database is using the simple recovery model. If the database uses the full or bulk-logged recovery model, truncation occurs automatically after each log backup.
Which recovery model your database should use depends on whether your recovery plan includes a requirement for point-in-time recovery. If not, then use the simple recovery model. Otherwise, SQL Server needs to use the full or bulk-logged recovery model and transaction log backups become a requirement (otherwise they will not truncate and will continually grow the log file).
Note: The default recovery model for SQL Server depends on the product edition. SQL Server Express defaults the recovery model to SIMPLE, while most other editions default to FULL.
Which brings me to the final topic of this blog – log file shrinkage. This should be an uncommon operation that is performed manually (usually as part of a database migration, bulk import, etc.), and not part of any maintenance or scheduled backup operation.