Shrinking the Transaction Log in SQL Server with DBCC Shrinkfile

Introduction

In SQL server a log file contains all the transaction details and it also stores all the transaction detail in ascending order. But if the transactions are growing continuously and a quick action is not performed by the user, it can be a cause of log file corruption. For the prevention of this issue there are some methods including the maintenance of the size of a log file. Removing the unused transaction logs from a log file increases the performance of SQL Server. The SQL Server provides a manual method, through SQL Server Management Studio we can maintain our log file data and the second method is shrinking the transaction log in SQL Server with DBCC SHRINKFILE. In this article we will discuss the second method of maintaining a log file data.

What is Shrinking the Transaction Log in SQL Server?

Transaction log shrinking is a method in SQL Server by which a user can shrink MS SQL transaction logs or a user can utilize that space which is already filled by unused transaction logs. If your transaction log file holds some unusable transaction logs which you do not need so you can utilize that space by shrinking the transaction log size. This method is called transaction log shrinking.

This method takes place only if the database is online and also if a minimum of one virtual log file is free.
If there is no virtual log file is available, the transaction log shrinking operation could fail or can until the next log truncation performed by the user.

Shrinking the transaction log in SQL Server depends on active or inactive virtual log files. The shrinking transaction log decreases its physical size by eliminating one or more than one inactive virtual log files.

Example: If we have a 600 megabyte transaction log file which we have been break into six 100 MB virtual logs, the size of this transaction log file can only be decrease in 100 MB increments.
The size of the file can be reduced as 500 MB or 400 MB but unable to reduce as 433 MB or 525 MB.

Mainly virtual log file holds active log records or we can say active virtual log file, hence it is a part of logical logs which cannot be removed.

How Transaction Log File Shrinks Through DBCC Shrinkfile?

The DBCC SHRINKFILE or DBCC SHRINKDATABASE action quickly shrinks the size of a physical log file to the requested size. The transaction log file shrink operation only eliminates the inactive virtual log files. If there is no target size is fixed, the transaction log file shrink operation eliminates the inactive virtual log files beyond the most recent active virtual log file in the file.

If the target size is fixed, shrinking the transaction log in SQL Server eliminates only sufficient inactive virtual log files to move forward but not go above the target size.

After performing the transaction log shrink operation the log file size can be larger than the specified size, but it cannot be smaller than the target size. You cannot shrink a file more than its specified size.

Syntax with its Arguments :

       DBCC SHRINKFILE 
       (
       {file_name | file_id} 
       {[ , EMPTYFILE ] | [[ , target_size ] [, { NOTRUNCATE | TRUNCATEONLY }]]
       }
       )
       [WITH NO_INFOMSGS]

Arguments:

1. The file_name is the logical name of the file on which the shrink operation is to be performed.
2. The file_id is the personal identification number of the file which is to be shrink.
3. The EMPTYFILE transfers all the data from one file to another file in a same filegroup.
4. The target_size is specified by the user and If the size is not specified, the DBCC SHRINKFILE decreases the size to its default size.

NOTE: The NOTRUNCATE and TRUNCATEONLY arguments are only applicable to data files, but they don’t affect the log file.

Conclusion:

The DBCC SHRINKFILE operation is mainly useful for a truncate table or a drop table operation because these operations create plenty of unused space. Most of the databases need some free space to perform normal day-to-day operations. Shrinking the Transaction log in SQL Server with DBCC Shrinkfile helps you to utilize the unused space on SQL Server.