Get to Know the Difference Between Truncating and Shrinking the Log File

  author
Written By Ashwani Tiwari
Anuraag Singh
Approved By Anuraag Singh
Published On October 15th, 2019
Reading Time 5 Minutes Reading

In SQL Server, the Transaction Log file stores the data related to all the transactions that are performed in a SQL database. 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. Now, most of the users are confused in such circumstance that how to maintain the space. So readers, stick your eyes in this blog, as we’ll discuss the procedure to manage storage space of the log file via truncating and shrinking process.

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).

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?

Shrinking the log file allows to lower the size of the transaction log file. However, it is not a 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

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 –

Q – Is it safe to shrink the SQL Transaction Log file?

A – The shrinking process does not have any harmful effect on the existing database.

Q – What is the Shrink Log file?

A – Use to reduce the size of a transaction log file less than its size at creation.

Q – What is the method to compress a SQL log file?

A – One can perform the shrinking procedure in SQL Server.

  author

By Ashwani Tiwari

Being a Chief Technical Analyst, I am aware of the technicalities faced by the user while working with multiple technologies. So, through my blogs and articles, I love to help all the users who face various challenges while dealing with technology.