Difference Between Shrinking and Truncating the Transaction Log

Introduction

The transaction log file stores information about all transactions that are performed on SQL database and their states. In case of data loss and damage, the user can recover their data from transaction log.

Sometimes transaction log file consumes considerable amount of space in SQL database, therefore Transaction Log file needs to be maintained. In order to manage the space, user can use Transaction log Shrinking and Truncating.

Comparing the Difference Between Shrinking And Truncating the Transaction Log

What If I Shrink My Transaction Logs ?

• With the help of shrinking, user can frees up the unused space and recover the excess space by reducing the size of transaction log. Shrinking the transaction log will reduces it’s virtual size by deleting the all inactive logs.
• If the user does not given any target size, a shrink file operation will removes all inactive virtual log files from transaction log. However, in case if target size is specified by the user, the shrink file operation will not exceeds it’s limit for removing the inactive logs.
• Shrinking the transaction log file is manual procedure and user can reclaim the disk space for other uses.
• User, must avoid shrinking of transaction log files consistently as it results fragmentation issue.
• User can perform shirking with DBCC SHRINKFILE command

Arguments:

file_name: it is a name of file to be shrunk

file_id: The file to be shrunk can be uniquely identified with file_id. To obtain the identity of the file, admin can use the FILE_IDEX system function.

target_size:If target size is given by user, the shrink operation will not exceed it’s limit while removing the inactive logs. It is the size for the file in megabytes, which is expressed as an integer. If user will not specify the value, DBCC SHRINKFILE will reduces the size of file to the default size.

EMPTYFILE:It will move all data from the specified file to other files present in the same filegroup.

NOTRUNCATE:It will migrate allocated pages from the end of a data file to unallocated pages in the front of the file.

TRUNCATEONLY:It will releases all free space at the end of the file to the operating system.

What If I Truncate My Transaction Logs File ?

• If the transaction log records fills all disk space, in that case log truncation will automatically frees the space for reuse by transaction log file.
• No need of manual effort is needed for truncation, as it automatically removes entries from transaction log.
• Whenever user backup the SQL Server database, the server will truncates the transaction log.
• The truncation occurs automatically under simple recovery model and full recovery model.
• Shrinking the transaction log file depends truncation.
• Log truncation does not reduce the size of transaction log file.
• Truncation only marks virtual log files inactive.
• If the virtual logs in a transaction log file consist logical log, then admin can not shrink the file until a truncation marks one or more of the virtual logs as inactive. After truncation mark virtual files inactive, a log shrink operation would remove inactive virtual logs to reduce the log file.

Conclusion:

In order to maintain the free space in transaction log file, shrink and truncate operation plays very important role. After comparing the both operations we can conclude, user can not perform shrinking until truncate operation mark virtual logs as inactive.