Guest Post: Shrink the SQL Database Log File!

For the first time I'm introducing a guest blogger through this post.
Priyanka Chouhan has gladly volunteered to be the guest blogger for us. She has come up with an informative article on shrinking log file which you can read below.

Introduction

SQL Server log files can often grow humongous in size and occupy a lot of space on your hard disk. Factors such as a long-running transaction or a paused database mirroring session can cause a transaction log to fill up quickly. As such, it is a good practice to keep the size of these log files under control by backing up and shrinking them regularly. Failing to do so could eventually put your database at a data loss threat due to lack of space and might raise the requirement of SQL database recovery tool.

Shrinking the transaction log file basically involves reclaiming the unused space within the log file by reducing it in size. The precious disk space thus salvaged can be utilized for other purposes. This guide will be covering the basic steps and also some good practices that you can follow while attempting to shrink the transaction log file.

Shrinking the log file – How does it work?

Let’s start with the basics. When a transaction log file is shrunk, its physical size on the disk is reduced since one or more inactive virtual log files associated with it are removed. The unit of the size reduction is always in terms of the virtual log file. For example, if you have an 800 MB log file that’s been divided into eight 100 MB virtual log files, the size of the log file can only be reduced in increments of 100 MB. In other words, the size of the file can be reduced to 600 MB or 700 MB but not 634 MB or 782 MB. Any virtual log file that does not hold any active log records is considered to be an inactive virtual log file and thus deemed fit to be removed via shrinking.

Like all other file shrinking operations, a transaction log shrinking operation frees space from the end of the file. Enough inactive virtual logs are removed to make the transaction file size fit the size requested by the user (see handy tips section).

Shrinking the log file – Command and Syntax

To shrink the transaction log file, the DBCC SHRINKFILE command is used with the following syntax:

DBCC SHRINKFILE ()

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE ()

Note: This is the most basic form of the DBCC SHRINKDB command. 
A lot of flags can be applied to this command to make it achieve more tasks. However, for our current illustration,the basic syntax will suffice.

Here is the detailed explanation of what each argument means.

log_file_name_Log – this indicates the logical name of the file that is to be shrunk

database – this indicates the name of the database to which the log file belongs

TRUNCATE_ONLY – this flag does the actual task of releasing all free space at the end of the transaction log file. It however, does not perform any page movement within the file. The file is shrunk only to the last allocated limit.

Shrinking the log file – Complete step-wise procedure

Now that we’ve gone through the command that does the trick, here’s a summarized step-wise account of how to shrink a large SQL Server transaction log file:

1.    As the very first step, back up your database. This is of utmost importance.

2.    Launch SQL Server Management Studio.

3.    Right-click on your database name and choose “New Query” to open up a query window with the large transaction log.

4.    Right-click on the database name, select “Properties”, and then in the “Files” screen copy the Logical Name (probably ends in <_log>.)

5.    Execute the DBCC SHRINKFILE command as shown in the previous section.

6.    At the end, perform a full backup of the database.

Few handy tips to prevent disasters

1.    The TRUNCATE_ONLY operation though shrinks the log file size dramatically, might inadvertently lead you to losing some valuable log file data at the end of the file. This is because it shrinks the truncated log file to the minimum size possible. Hence, a safer way would be to explicitly specify the target size for the file after shrinking. This can be done through the following parameter:

target_size – indicates the desired transaction log file size (after truncation) in MBs. The target size is expressed as an integer which if not supplied to the command is reduced to the default file size (mentioned at the time of creation of the file).

Note: The “target_size” parameter is ignored if specified with TRUNCATE_ONLY hence only use one at a time.

2.    Few DBAs make the mistake of executing chain of commands wherein they first set the database to simple recovery mode, then shrink the log file and then set the database back to full recovery mode. While this may theoretically seem correct, it might in fact cause more damage than help. By setting the database to full recovery mode after shrinking the log file you might end up losing valuable log data and might not even be able to restore point in time. Plus, you might not be able to use subsequent log files i.e. it will break the log file chain.

Summary

So now that you’ve learnt how to shrink the transaction log file, you shall use the above mentioned steps to reduce the log file to a size that’s much smaller than its original size. However, as shown above, the best practice is to regularly backup the log file to avoid oversized file growth.

About Author

Priyanka Chouhan is a technical writer in “Stellar Data Recovery “with 5 years of experience and has written several articles on SQL. She has in-depth knowledge on SharePoint and SQL Server. In the spare time she loves reading and gardening.