How SQL Server Transaction Log Backup, Truncate and Shrink Functions Operate

SQL server has become one of the most powerful database tools to use in the industry. Many of its functions are exceptional in terms of protecting your data, including the SQL Server Transaction Log backups, truncating, and shrink operations. This article looks at this operation, providing some examples to further explain the processes.

To get started, it is important that you have a foundation of understanding related to the SQL Server Transaction log. This plays an important role in being able to understand these processes more clearly so that the state of the database is sound and that you are able to recover corrupted data or fix a table that has been modified in error.

Transaction Log Backup

Starting with the Transaction Log Backup, the goal is to create a simple recovery model. In this process, the SQL Server Transaction Log is marked as inactive and will be truncated automatically after an active transaction is committed. This is not what is found in either Full or Bulk-Logged recovery models. In the Full model, the Transaction Log is marked as inactive, but there is no automatic truncation of the data once the transaction is committed. What this means is that only the Transaction Log is backed up, but not the entire database.

The goal is to ensure that the Transaction Log is backed up after each transaction. When this does not occur, the Transaction Log file continuously grosses with no truncation. This will occur until it runs out of free space, which can cause issues with your database and the efficiency of its operation.

Before proceeding, it is important to understand that the SQL Server Transaction Log backup is only accessible from the database once the recovery model of the database is in Full or Bulk-Logged mode. This is performed by going to the Options tab in the Database Properties window. It is also important to note that if you attempt to take the Transaction Log backup from the database while the program is in Simple recovery mode, it will fail, and you will receive an error message.

You must also understand that the Transaction Log backup requires that you have at least one Full backup that has been performed. This provides a starting point for the new backup chain to begin. If you have not performed this full backup already, then the operation will not work, and you will receive an error message.

To access the Transaction Log backup, the best way is to use the BACKUP DATABASE T-SQL command. Using this command, the Transaction Log will take the backups of all transactions that have occurred since the last Full backup was performed. You can also access the backup using the BACKUP LOG T-SQL command.

If you have performed more than one Full backup, then using these commands will access the Transaction Log backup since the last backup was performed. However, you are able to access previous versions of the backup. This allows you to choose a specific point where a backup was performed and re-activate that backup.

If you are in a situation where the database has been damaged in some way, then it is recommended that you create a tail-log backup that will help to restore the database to the current date. This tale-log backup is used when you need to capture all of the log records that have not been backed up to this point. This will help to ensure that no data loss occurs.

Here is a common example. Someone uses the DELETE statement in a certain record or database without using the subsequent WHERE clause. In this case, all records associated with the delete from our remote. This could be thousands of records. If you have performed a backup, you can easily recover this data by restoring the backup at the point that the delete statement was used.

One of the benefits of using this backup function is that you can access the exact date and time when you need to restore the records. So, for example, if you know that the delete command was used at 8:00 a.m. on August 7, you can restore the closest backup to that date to retrieve all records that may be lost.

Transaction Log Truncate

When using the Transaction Log Truncate command, you are marking records that are inactive so that they can later be deleted. Configuring this in the Transaction Log is easily performed by using the Simple recovery model. This can be configured using either Full or Bulk-Logged recovery models. You are able to activate the Transaction Log backup process so that the database is truncated automatically.

When you use the TRUNCATE_ONLY Transaction Log backup option, the database backup chain is broken, allowing files to be truncated through the Transaction Logs. This function is only available in SQL Server 2008 or in previous versions.

Transaction Log Shrink

The benefit of using the truncation process is that this frees up additional space within the database. Now you have space that can be reused to increase the number of files and data that you are able to maintain. However, the Transaction Log file is not decreased in size. This is to ensure that records are properly maintained and that no data is corrupted.

This is also done to ensure that the Transaction Log file shrink process can be initiated. If there is not enough free space for the Transaction Log file, then the process will not occur. This is why it is essential that truncation is done on a regular basis so that there is plenty of room in the backup area for the Transaction Log files.

If you are looking to activate this process, go to the Shrink File page and change the File Type II Log. Here you will find that there are three options that are available, including releasing unused space in the Transaction Log file so that the file is shrunk to the last allocated extent freeing unused space in the Transaction Log file so that Rose can be reallocated, or that all data is moved to other files that are contained within the same filegroup.

You can also shrink the Transaction Log file by using the DBCC SHRINKFILE T-SQL statement. It is important to note that you cannot shrink the Transaction Log file to a smaller size than that of the Virtual Log File. It will not allow you to do this.

With this information, you should clearly understand how to use these three processes.