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 Truncating and Shrinking 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, that both truncate and shrink are not the same, but different. Now, you might be thinking that what is different in both?

Well! Truncate process modifies VLF or Virtual log file from active to inactive state. Whereas, the Shrinking process bring downs the physical log size to a smaller or manageable size.

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 as Virtual log files(VLF). Understanding the syntax to truncate and shrink SQL log file will help users in depth.

Well! Truncating the log file means deleting the actual log file. In case, the log file is not truncated on a regular basis, then all the space will be occupied that is been 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.

Truncate 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 VLF can be changed automatically from active to inactive state. However it will depend completely on the recovery model and backup type.

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

We can use Truncate_Only or With No_Log command to truncate the log file in SQL server. Following is the syntax to truncate 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 free-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 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 files 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 few tips involved to avoid from shrinking, which are,

1. Ensure to run the log backup frequently and also manage the log file size as small.
2. Try to run differential backup 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 to shrink the transaction log file, 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 the commands is corruption or data loss. Mostly in the truncated CMD, users tend to face major data loss issues. Shrinking the log file can result in the same in several cases. The only way for users to get rid of such severe problems is by opting for a smart solution like SQL Log Analyzer utility.

This advanced utility can not only fix the 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 file from active to inactive and denotes the space for reuse.

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

Frequently Asked Questions –