SHRINK LOG File SQL

SQL Server Database Log File has grown exponentially over a period of time and now causing DISK space issue, how to reduce | Shrink Log file (.ldf) size?

Steps to truncating log files and shrinking your database

1. Get logical / physical name of your database log file (LDF)
2. Truncate LOG File
3. Shrink LOG File

Let's see how we SHRINK LOG File using TSQL

STEP 1 - Get logical / physical name of your database log file

use <[your database name]>
exec sp_helpfile

STEP 2 - Truncate LOG File

-- replace <[your database logical log filename]> with Logical file name, which we get in STEP 1
USE <[your database name]>
GO
BACKUP LOG <[your database logical log filename]> WITH TRUNCATE_ONLY
GO

STEP 3 - Shrink LOG File

-- replace <[your database logical log filename]> with Logical file name, which we get in STEP 1
USE <[your database name]>
GO
DBCC SHRINKFILE (<[your database logical log filename]>, 1) 
GO

Let's see how we

SHRINK LOG File using SQL Server Management Studio (SSMS)

What are the possible solutions, if my transaction log is FULL?

• Backing up the log



• Freeing disk space so that the log can automatically grow.



• Moving the log file to a disk drive with sufficient space.



• Increasing the size of a log file.



• Adding a log file on a different disk.



• Completing or killing a long-running transaction.

Why the size of LOG file is keep growing ?

Every SQL database has a transaction log that records all transactions and database modifications made by each transaction. If log record is never deleted from transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files.

BUT WHY SQL Server is keeping track of every transaction in transaction log file?

It's a design of SQL Server, which uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record. Thus transaction Log supports

• Recovery of any individual transaction



• Recovery of all incomplete transactions when SQL Server is re started



• Rolling a restored database forward to the point of failure

Can we disable this Write ahead operation to avoid disk filling operation ?

NO, SQL Server always performs a write log operation but we can set a database recovery model to SIMPLE, which will automatically truncate the transaction log on CHECKPOINT.

Check my previous post, "difference between SIMPLE and FULL recovery Model" and "SQL Server Recovery Model" to know more about SQL Server recovery models.

When size of the log file is physically reduced?

There are three events, which forces SQL Server Database file to Shrink

• When a DBCC SHRINKDATABASE statement is executed.



• When a DBCC SHRINKFILE statement referencing a log file is executed.



• When an autoshrink operation occurs

Is Truncate LOG File and Shrink a log file is same thing ?

NO, both are altogether different operation. Shrinking means releasing free space to the Operating System whereas process of deleting log records to reduce the size of the logical log is called truncating log.

If a LOG file is 100% used and active then Shrink operation will reduce the size of the transaction log file.

Here are some facts about SHRINK and TRUNCATE

• Shrinking a log is dependent on first truncating the log



• Log truncation does not reduce the size of a physical log file



• Log truncation reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.



• A log Shrink operation removes enough inactive virtual logs to reduce the log file size to the requested size, requested size can not be less than inactive size.

To make it more clear, lets take an example, if you have a 10 GB log file that has been divided into 10 1 GB virtual logs, then the size of the log file can only be reduced in 1 GB increments.

The file size can be reduced to 1 GB sizes such as 1 GB, 2 GB, 3 GB .. 9 GB , but it cannot be reduced to sizes such as 1150 MB or 2500 MB as Virtual logs that hold part of the logical log cannot be freed.

NOTE: So to reduce a log file size, we first need to truncate and then only we can shrink log file.

I am Truncating and Shrinking LOG file but size is not getting reduced, What is possible cause ?

If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive. When any file is shrunk, the space freed must come from the end of the file.

How frequently I should SHRINK Database LOG File ?

Ideally, you should not be shrinking your LOG files, if you have followed all Best Practices, which I mentioned in the last of the article.

I can think of only two frequent scenarios where I go ahead and shrink log file

1. My database is full recovery model and transaction log is never being backed up, which made my log in GB's TB's. I have see a database where a data file is MB's and transaction log was in GB's. To correct this issue we need to regular backup transaction log.



2. On demand large BULK INSERT, which filled up transaction log.

Is it GOOD to shrink Database Log file ?

Without a valid business or technical reason, we should shrink database log. If you shrink your log and log file fills up then it would auto grow but that is as additional delay to your transactions.

If you are trying Just to keep a disk space free and available for others to use then definitely this is not a good thing to do.

What is the best practice to manage the SQL Log files ?

Database LOG File Best Practice

• Keep transaction number low I would prefer single log file, if possible, this help during recovery.



• Place the LOG file on the fastest available redundant disk volume



• Frequently backing up the log frequently, depends on business data recovery policy



• Initialize the database log file with required space to avoid auto grow during production hours.



• Place a log file on a larger DISK volume so that the log can automatically grow, incase that is required.



• Keep your VLF count low



• Defrag the disks on which your tranaction logs reside to get rid of disk file fragmentation.  (requires downtime)



• Back up the 'tail of the log' in a disaster scenario if possible