Truncate and Shrink Transaction Log: What To Choose?


Transaction file is the most vital element yet overlooked sometimes. The transactions carried out in the SQL Server database are recorded and logged into the transaction log database. This is why the file is named ‘Transaction Log Database’. There exists an individual Transaction log file for each database managed on the server. Therefore, it there are high chances that the storage of this transaction files might exceed the maximum limit. Thus, it is necessary to keep a check on the storage of the transaction log and maintain it by cutting it short. The following segment discusses in detail about two such procedures that render the reduction of Transaction Log file size. Truncate and Shrink transaction log are the two respective procedures that can be implemented for achieving the reduction of Transaction Log file size.

Necessity to Manage Transaction Log Files

Transaction log files store enough information about the transactions carried out in the past to both; understand/examine the database activities and rollback the DB to an earlier state. The log file records a transaction from its very beginning along with the changes that occur in between until the final commit.

Every database is allotted a minimum of one transaction log file that is implemented to use for the defined purposes. You can also recover deleted SQL Server data from transaction log file. Therefore, Transaction Log file management is very important from an administrative standpoint.

Difference Between Truncate and Shrink Transaction Log

Executing both the actions does more or less the same thing, i.e. reduces the Transaction Log file size. However, both truncate and shrink transaction log follow a different approach each, for doing so. The way that the Transaction Log file size is reduced is different in each method.

Shrinking Transaction Log

Shrinking Transaction Log is a procedure where the log file size is physically decreased via eliminating as many virtual log files in an inactive state, as there are in a transaction log. Virtual log files holding on to any active virtual log is actually a part of the logical log, therefore, it cannot be removed.

The file shrinking operation only helps in the removal of virtual log files from the server that are inactive and not the active ones. On not having specified the size of target, the shrink operation automatically removes the virtual logs in an inactive state beyond the virtual logs in the file that were last active.

However, on specifying the target file size only the remove of as many inactive virtual logs is done that are enough to help reach the target size and not exceed it. Once the shrink has been performed, the log file becomes slightly larger but never smaller than the size specified as target. However, it is difficult to predict exactly how much will the log file shrink. DBCC SHRINKFILE is used for physically shrinking log files.

NOTE: On shrinking, the space that is freed in the log file must be at the end of the file always.

Truncate Transaction Log

Log records when never cleared from a transaction log file, logical log files continue to grow until the available storage space on the disk holding physical logs, reaches its maximum.

TIP: Log files are important from associated database restoration point of view. However, at times, the log records that date back to a very old time must be deleted as; these records are no longer required for database recovery or restore purposes. This instead creates room for new log records to be stored.

This deletion procedure that takes place for generating space for new log records to be stored is known as Log file Truncation.

In this procedure, the active part of transaction log file is not meant for truncation. The active part is required for recovering the associated database. Therefore, the portion can anytime be required in future.

Conclusion

As per the observation, both the functions are somewhat similar but render an output differently. Shrink performs the removal of inactive virtual logs to make the transaction log files manageable whereas the records are cleared off in the case of truncation. Therefore, it is necessary to implement the suitable option (Truncate and Shrink Transaction Log) according to the need.