First, we should know what is transaction log file in SQL Server and what is the difference between truncating and shrinking of log file? Let’s discuss these terms and know how to perform the truncating and shrinking operations on SQL Server transaction log files.
What is Transaction Log?
The transaction log is a brittle part of any database. Every SQL Server database has an associated log file that holds all the information of every transaction and database modification done by each transaction. In case of system failure, transaction log file is helpful to bring back the database in healthy and consistent state.
Difference Between Truncate and Shrink Transaction Log File
Generally it is seen that most people live in the illusion that the terms; truncating a log file and shrinking a log file are same. However, both the terms are quite different from each other. Shrinking reduce the physical log file size and Truncate changes virtual log file from active to inactive status.
How to Truncate SQL Server Transaction Log File
Log truncation is used to keep the log files from filling up. Log files are partitioned in smaller chunks known as Virtual Log Files. The deletion of active virtual log files is done with log truncation process. If a transaction log is not truncated regularly, it will occupy all the space that is allocated to the physical log file.
The truncation process does not overwrite the log files, it just clears the log files from the disk. This means that the this log file is not required anymore and can be overwritten. If any virtual log file that contains more than one log record must not be overwritten and marked as “active”, if VLF does not contain any record, then the VLF is marked as “inactive” and can be reused safely.
It is possible that we can change the virtual log file state automatically from active to inactive mode, but the changing of VLF state is totally depends on your recovery model and backup settings.
The events after which VLF state changes from active to inactive state:
If a checkpoint occurs in simple, full or bulk recovery model and you perform a truncate operation, then VLF state changes from active to inactive and now you can reuse it automatically.
You can use TRUNCATE_ONLY or With NO_LOG statement to truncate SQL Server transaction log files.
Syntax:
USE Database_name
DBCC SHRINKFILE(TestDBLog, 1)
BACKUP LOG TestDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TestDBLog, 1)
GOHow to Shrink Transaction Log File
Shrinking a log file is not a good practice because the shrinking process reduces the physical size of a log file. It also empties the file by moving it from one location to another location in the same filegroup and allows the file to be removed from the database. Shrinking a log file means resetting the file size into the smallest size.
You can use DBCC SHRINKFILE statement to shrink transaction log file.
Syntax:
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT]
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT]
GONote:It is recommended that try to truncate the log file instead of shrinking because shrinking increases the fragmentation.
Conclusion
In this article, I have summarized the SQL Server truncate and shrink transaction log file and what is the difference between truncating and shrinking a log file. However, truncating a log file is a good practice over shrinking a log file because the truncating process frees the space in virtual log files while the shrinking file reduces the physical log file size which will be full after a short duration.
EmoticonEmoticon