How to shrink the size of a transaction log (.LDF) in SQL (4274975)

SQL Transaction logs will grow indefinitely if left unchecked and can eventually use up all available space on the volume that contains the transaction log.

Shrinking transaction logs should be done regularly as part of a SQL database maintenance plan.

Transaction log growth occurs naturally with a SQL DB in Full Recovery mode. To keep a lid on the size of the LDF file, regular transaction log backups should be made (at least weekly). Once a transaction log has been backed up, it frees space within the existing file for reuse so the LDF file should stabilize at a certain size (e.g. 1 weeks worth of transactions) and not really grow much between backups. If the size that the LDF stabilizes at is considered too large a more frequent transaction log backup will be necessary.

Consult the product documentation for recommendations on transaction log backup frequency.

Note: The steps within this knowledgebase article are provide as is and should be performed by a Database Administrator. Should you require any assistance with the mentioned Database administration tasks, please reach out to Microsoft SQL Technical support for assistance.

Disclaimer: The information in the script(s) provided is known to work successfully; however, they have not been officially tested by Quest Software Quality Control.

Shrinking an LDF is a 2 step process.

Perform one of the following, either:

- Backup the transaction log using a SQL aware backup solution (e.g. BackupExec, SQL Management Studio etc.)

OR 

- Truncate the transaction log using a SQL query. Truncating the log means it will only be possible to restore to the last known Full SQL backup.

Backing up or truncating the log file creates whitespace and zeros out the log file making it available for reuse, i.e. the physical LDF size will remain the same but SQL will start reusing the existing LDF. The LDF will only start growing again once all the existing space within it has been utilised.

This is the process and commands to actually shrink the file.

1.Open a new query for the database that is to be worked on, ensure the correct database is displayed in the dropdown box.

2. Run the following query to display transaction log size and usage percent, locate the correct row for the database concerned and note the values.

DBCC SQLPERF (LOGSPACE)

3. Truncate or backup the transaction log using a SQL query.

a) If using backup software to backup the log file skip to step 3.

IMPORTANT: If this command is used a Full backup of the database must be made at the earliest opportunity subsequently.

b) Run the following command to truncate the log file, replacing DATABASENAME with the name of the SQL database being worked on.

BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY

(e.g. use: "BACKUP LOG MessageStats WITH TRUNCATE_ONLY" if the DB name is MessageStats.)

Note: On SQL 2008 R2 and newer the " WITH TRUNCATE_ONLY" is no longer a valid command. To truncate the log files place the database recovery mode in simple mode.

Shrink the physical LDF file and return the space to the file system.

a) Determine the logical name of the log file by running the following command. Locate the row where the Usage column says "log only"
and make a note of the value in the "Name" column.

EXEC sp_helpfile

b) Run the following query to display transaction log size and usage percent, locate the correct row for the database concerned.

DBCC SQLPERF (LOGSPACE)

c) If the Truncation or backup of the transaction log was successful the "Log Space Used" should be a very small percentage of the total
Log Size and smaller than noted in step 1a. Now calculate the target file size for the transaction log, do this using
the following formula:

d) Shrink the LDF using the command below, replacing TARGETSIZE with the number derived in Step 3c and LOGICALNAME with the
name noted in Step 3a.

DBCC SHRINKFILE (LOGICALNAME,TARGETSIZE)

--e.g. DBCC SHRINKFILE (MessageStats_log,3072) will shrink the MessageStats LDF to 3GB.