Difference Between Truncating and Shrinking the Log File of SQL Server

In SQL Server, the Transaction Log file stores the data related to all the transactions that are performed in a SQL database.  SQL Database consists of several commands out which truncating and shrinking database log in SQL are there also. In case of any disaster when the data gets lost or damaged in any way, then one can easily recover the data with the help of SQL Server transaction log file.

At certain times, the log file consumes a particular amount of space in the SQL database. Hence, the transaction log file needs to be managed properly in order to access it efficiently with truncate and shrink SQL log file techniques. Now, most of the users are confused in such circumstances that how to maintain the space. So readers, stick your eyes on this blog, as we’ll discuss the procedure to manage the storage space of the log file via truncate and shrink SQL log file.

Difference Between Shrinking and Truncating Database Log File

Often times, it is observed among many users that they have kept a mindset that truncating and shrinking the log file is the same. So let this illusion be cleared in this blog: truncate and shrink are not the same, but different. Now, you might be thinking, what is different in both?

Well! The truncate process modifies VLF or Virtual log files in SQL Server from active to inactive state. Whereas the Shrinking process brings down the physical log size to a smaller or manageable size.

Also Read: How to Clear SQL Server Database Transaction Log File? 3 Best Ways

What is the Syntax to Truncate Log File in SQL Server?

Generally, the log file is truncated to keep the log file from filling up. Here, the transaction log file is divided into smaller chunks called Virtual Log Files (VLFs). Understanding the syntax to truncate and shrink SQL log file will help users in depth. Let’s now understand Truncating and Shrinking in detail to make it easier for the users to figure out the distinction.

Well! Truncating the log file means deleting the actual log file. If the log file is not truncated on a regular basis, then all the space will be occupied that is allocated to the physical log file. When the truncate command is implemented, it will overwrite the log file, instead it deletes the log file from the disk.

The truncate SQL Log file process will change one or more Virtual log file status from active to inactive, by which the storage space is marked for reuse. Now, the status of these files can be changed automatically from active to inactive state. However, it will depend completely on the recovery model and backup type.

SQL Server runs a checkpoint. If it occurs in the simple, full, or bulk recovery model, then a user is allowed to execute the truncate process. In which, the status of the file is changed from active to inactive, as now a user can also reuse it automatically.

We will now take a look at truncating and shrinking steps one by one. We can use the Truncate_Only or WITH NO_LOG command to truncate the log file in SQL Server. The following is the syntax to truncate the transaction log:

DBCC SHRINKFILE(FirstDBLog, 1)
BACKUP LOG FirstDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(FirstDBLog, 1)
GO

One can change the name of log file (FirstDBLog).

What Happens If the Log File is Truncated?

• If the log file, which consists of numerous records, fills up space, then truncating will be the best option, as it frees up the space to reuse it again.
• When the backup is done for the SQL Server database, then the server does the truncate process of the log file.
• When the log file is truncated, it only marks the VLF status as inactive.

What is the Syntax to Shrink Log File in SQL Server?

To understand truncate and shrink log files in SQL Server, we must equally study both commands. Shrinking the log file allows you to lower the size of the transaction log file. However, it is not the best option to shrink the log file. This is because it not only empties the file but also transfers it from one location to another in the same filegroup. Shrinking itself means resetting the log file size to a compact size.

There are a few tips to avoid shrinking, which are,

1. Ensure to run the log backup frequently and also keep the log file size as small.
2. Try to run differential backups in simple recovery model, if the organization is ready to accept some data loss. This will manage the log file to maintain the size limit.
3. Try to alter the database and move the log file by adding a new drive or transfer it to another drive.

DBCC SHRINKFILE statement enables you to shrink the transaction log file; the following is the command for the same,

Use [master]
Go
ALTER DATABASE [DB_Name] SET RECOVERY SIMPLE WITH NO_WAIT]
DBCC SHRINKFILE (DB_Name, 1)
ALTER DATABASE [DB_NAME] SET RECOVERY FULL WITH NO_WAIT]
GO

Biggest Drawback of TRUNCATE & SHRINK Tasks

The major drawback in both commands is corruption or data loss. Mostly in the TRUNCATE command, users tend to face major data loss issues. Shrinking the log file can result in the same in several cases. The optimal way for users to get rid of such severe problems is by going for a dedicated solution like SysTools SQL Log Analyzer Tool.

This advanced utility can not only fix corruption & data loss issues but is capable of much more. Analyzing the log files for various purposes like auditing, threat identification, etc. The best about this is that users can get the desired results because of the advanced features. This enables users to get customized results as well.

Observational Verdict

In this blog, we have clearly discussed What is the difference between shrinking and truncating database log file in an understandable way. The shrinking process reduces the physical log file size, and truncate changes the status of one or more VLF files from active to inactive and denotes the space for reuse.

This is because truncating improves and frees up the space, whereas shrinking increases fragmentation. So, if the user faces an oversized log file, then perform any of the above processes to deal with it.

Frequently Asked Questions –

Also Read: How to Restore Transaction Log Grayed Out in SQL Database?