In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed

If this article is your first visit to the SQL Server Transaction Log series, I recommend you to check the previous articles (see the TOC below), in which we described the internal structure of the SQL Server Transaction Log, the vital role that the Transaction Log plays in keeping the database in a consistent state and recovering the corrupted database or mistakenly modified table to a specific point in time. We discussed also in this series the three recovery models, Full, Simple and Bulk-Logged, that controls how the transactions will be written to the SQL Server Transaction Log file and finally how to manage and monitor the SQL Server Transaction Log growth.

Building all the basic information from the previous articles, we are ready now to discuss deeply in this article the difference between the SQL Server Transaction Log backup, truncate and shrink concepts and how to perform these operations.

Transaction Log Backup

When configuring your database with the Simple recovery model, the SQL Server Transaction Log will be marked as inactive and truncated automatically after committing the active transaction. This is not the case with the Full and Bulk-Logged database recovery models. When the database is configured with Full recovery model, the SQL Server Transaction Log in the Transaction Log file will be marked as inactive after committing the transaction, without being truncated automatically, as it will be waiting for a Transaction Log backup to be performed. Recall that only the Transaction Log backup, but NOT the database Full backup, will truncate the Transaction Logs from the Transaction Log file and makes it available for reuse. If no Transaction Log backup is taken from the database, the Transaction Log file will grow continuously, without truncation, until it runs out of free space.

The SQL Server Transaction Log backup can be taken only from the database when the recovery model of that database is Full or Bulk-Logged. The recovery model of the database can be checked form the Options tab of the Database Properties window, as below:

If you try to take Transaction Log backup for a database that is configured with the Simple recovery model, the backup operation will fail with the error message below:

In addition, the Transaction Log backup requires that at least one Full backup is taken from that database as a start point for the new backup chain. If you try to take a Transaction Log backup from a database with no Full backup taken previously, the backup operation will fail with the error message below:

Let’s take a Full backup for the database to be able to take Transaction Log backup for that database. We will use the BACKUP DATABASE T-SQL command to perform the database Full backup operation in our example here. For more information about the different ways and options for performing database backups in SQL Server, check the SQL Server Backup and Restore Series. The Full backup of the database can be taken using the T-SQL script below:

Once the database Full backup is performed, we will start taking the Transaction Log backups for the database. The first Transaction Log backup will take a backup for all the transactions that occurred in the database since the last Full backup. The Transaction Log backup can be taken using the BACKUP LOG T-SQL command below:

On the other hand, the Transaction Log backups that follows the first Transaction Log backup will take backup for all transactions that occurred in the database since the point that the last Transaction Log backup stopped at. The Full backup and all following Transaction Log backup until a new Full backup is taken is called Backup Chain. This backup chain is important to recover the database to a specific point in time, in the case of any mistakenly performed change or database corruption. The frequency of the Transaction Log backup depends on how important your data is, the size of the database and what type of workload this database serves. In the heavily transactional databases, it is recommended to increase the frequency of the Transaction Log backup, in order to minimize the data loss and truncate the Transaction Logs to make it available for reuse.

If the database is damaged, it is recommended to create a tail-log backup to enable you to restore the database to the current point in time. A tail-log backup is used to capture all log records that have not yet been backed up. This will help in preventing any data loss and to keep the log chain complete.

Assume that you have executed the below DELETE statement by mistake without providing the WHERE clause. This means that all table records will be deleted:

If you have designed a proper backup solution, the data can be easily recovered by restoring the database back to the specific point in time before executing the DELETE statement. From the Restore Database window, the SQL Server will return the complete backup chain that is taken from that database. If you know the exact file that is taken directly before the data deletion, you can stop at that specific file, as shown below:

But if you are aware of the exact time of executing the DELETE statement, you can restore the database back to that specific point in time before the DELETE statement execution, without the need to know which Transaction Log file contains that point in time. This can be achieved by clicking on the Timeline option, and specify the time, as shown below:

Transaction Log Truncate

SQL Server Transaction Log truncation is the process in which all VLFs that are marked as inactive will be deleted from the SQL Server Transaction Log file and become available for reuse. If there is a single active log record in a VLF, the overall VLF will be considered as active log and cannot be truncated.

The SQL Server Transaction Log, for the database that is configured with the Simple recovery model, can be truncated automatically if:

• A Checkpoint operator is triggered
• The database transaction is committed

The SQL Server Transaction Log, for the database that is configured with the Full or Bulk-Logged recovery model, can be truncated automatically:

• After performing a Transaction Log backup process, and the Transaction Log is not waiting for an active transaction or any high availability feature, such as Mirroring, Replication or Always On Availability Group

• Change the database recovery model to Simple
  For example, if we change the recovery model of the below database to Simple and perform a Checkpoint directly, the Transaction log will be truncated automatically and will be available for reuse as shown below:

  

• TRUNCATE_ONLY Transaction Log backup option, that breaks the database backup chain and truncates the available Transaction Logs. (Available only prior SQL Server 2008.)
  If you try to truncate the Transaction Log of the database using the TRUNCATE_ONLY option in a SQL Server instance on version 2008 and later, the statement will fail with the error message below:

  

Transaction Log Shrink

When the database Transaction Log file is truncated, the truncated space will be freed up and become available for reuse. But the Transaction Log file size will not be decreased, as the truncated space will not be deallocated. On the other hand, the process of recovering the Transaction Log space by deallocating the free VLFs and returning it back to the operating system is called a Transaction Log Shrink. operation.

The Transaction Log file shrink operation can be performed only if there is free space on the Transaction Log file, that can be available most of the time after truncating the inactive part of the Transaction Log. A shrink operation will be useful after performing an operation that creates a large number of Transaction Logs.

The Transaction Log file of a database can be shrunk by right-clicking on the database and choose the Shrink -> Files option from the Tasks menu, as shown below:

In the Shrink File page, change the File Type to Log, and choose the Transaction Log file that you manage to shrink. In this page, you have three options:

• Release unused space in the Transaction Log file to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data
• Release unused space in the Transaction Log file to the operating system and tries to relocate rows to unallocated pages. Here, a value should be specified

• Moves all data from the specified file to other files in the same filegroup, in order to delete the empty file later

  

The same Transaction Log file can be shrunk using the DBCC SHRINKFILE T-SQL statement below:

Shrinking the Transaction Log file to a size smaller than the size of the Virtual Log File is not possible, even if this space is not used. This is due to the fact that the Transaction Log file can be shrunk only to the boundary of the VLF. In this case, the SQL Server Database Engine will free as much space as possible, and then issues an informational message, as shown below:

In the next article of this series, we will discuss the best practices that should be applied to the transaction log in order to get the optimal performance from it. Stay tuned!

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021