How to Shrink Log File in SQL Server? – A Detailed Guide!

Published On - June 27, 2025

SQL Server transaction log file plays a vital role in tracking all changes made to the database. However, these files grow bigger in size over a specific time. Irregular maintenance or proper backups can lead to consuming excessive disk space.

Hence, the blog will provide the most practical methods to shrink log files in SQL Server to avoid any potential errors related to storage space.

Why SQL Server Log Files Grow So Large? – Know Real Reasons!

Here are the common reasons behind the increased size of your SQL Server log files:

Uninterrupted transactions or long-running transactions keep log space occupied.
Failure to back up transaction logs files in full or bulk-logged recovery model leads to increased file size.
Improper or poorly configured Autogrowth settings cause the rapid expansion of log files.
Data-intensive operations like bulk inserts or large batches will increase the log activity.

Smart Shrink Strategy: Right Time to Shrink Log File in SQL Server!

The SQL shrink database process is a one-time corrective action, not a maintenance routine. Here’s when to use it:

Shrink Log File When:

Disk space is urgently needed.
A one-time bulk operation enlarged the log file.
You have switched from Full to Simple recovery.

Avoid Shrinking When:

You haven’t backed up the log file in Full recovery mode.
You want to free up log space permanently (it will regrow again).

Pre-Shrink Checklist: Prepare These Things Before You Shrink!

Follow these essential tips before you shrink database log file to avoid data loss and performance issues.

Check Active Transactions in SQL Server:

DBCC OPENTRAN

Verify Recovery Model of SQL Server:

SELECT name, recovery_model_desc FROM sys.databases WHERE name = ‘YourDatabaseName’;

Backup Transaction Log (if in Full/Bulk-Logged model):

BACKUP LOG YourDatabaseName TO DISK = ‘C:\Backup\YourDB_LogBackup.trn’;

Check File Size:

DBCC SQLPERF (logspace);

Top Ways to Shrink Log File in SQL Server – No Tools Required!

We have searched a lot and came up with these effective native solutions to perform the MS SQL shrink database process. These methods are ideal for Database Administrators and developers who prefer complete control.

Method 1. Shrink Database Log Files Using SSMS

Go through the following steps to perform the SQL shrink database procedure using SSMS.

NoteUse only when you have already backed up the log file.);

Open SQL Server Management Studio (SSMS).
Connect to the appropriate database instance.
Expand Databases and right-click on your database.
Go to Tasks > Shrink > Files.
In the File Type, select Log, and choose Release unused space.
Specify target size. Then, click OK to execute the shrink.

Method 2. Use T-SQL Query for DB Shrink in SQL Server

Here are the detailed steps to execute the MS SQL shrink database process via T-SQL Query:

Find the logical name of the log file using the given query.
SELECT name FROM sys.master_files WHERE type_desc = ‘LOG’ AND database_id = DB_ID(‘YourDatabaseName’);
Run the following query to shrink the log file.
USE YourDatabaseName;
DBCC SHRINKFILE (YourLogicalLogFileName, 1); — 1 MB target size

Method 3. Temporarily Switch to Simple Recovery (Optional)

Follow the given steps to shrink log file in SQL Server without taking any log file backups.

Caution: This will break the log chain, so use it only when you don’t need log backups.

Set the recovery model to SIMPLE with the given query.
ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
Run the following query to execute the SQL shrink database process.
DBCC SHRINKFILE (YourLogicalLogFileName, 1);
Now, Set the database back to FULL Recovery Model using the provided query.
ALTER DATABASE YourDatabaseName SET RECOVERY FULL;

Method 4. Shrink Database Log Files at Once [Advanced Solution]

Here is the step-by-step guide to shrink all database log files at once.

Note: Use this method with caution and only when necessary.

Run the SQL query to shrink all the databases at once.

EXEC sp_MSforeachdb ‘
USE [?];
IF EXISTS (SELECT name FROM sys.database_files WHERE type_desc = ”LOG”)

BEGIN
DBCC SHRINKFILE ((SELECT name FROM sys.database_files WHERE type_desc = ”LOG”), 1);
END’;

It will start shrinking the database log files. Wait till the process ends.

SQL Server Shrink Log File Best Practices You Shouldn’t Ignore!

Check out the expert-backed tips to perform the MS SQL shrink database process safely and effectively.

Always back up the log before shrinking.
Avoid frequent shrinks to reduce file fragmentation.
Use alerts and monitoring tools to manage log growth.
Set appropriate autogrowth values to avoid potential risks.

Key Notes

The blog has covered all the ways to shrink log file in SQL Server using manual methods. Its step-by-step illustrations will help you easily shrink database files. Besides that, if you want to restore data from a corrupt or damaged SQL Server, use automated tools like Recoveryfix for SQL Database Recovery. The SQL Database Recovery software also saves data directly to SQL Server, CSV file, or SQL Scripts. Take the free trial today.

FAQs

Q. How to shrink log file in SQL Server without backup?

A. You have to change the recovery model to SIMPLE to shrink log files without any backup. But you will lose the log chain during the process.

Q. Why is the SQL Server log file not shrinking?

A. There are specific reasons that stop the shrinking process of SQL log files, including active transactions and missing log backups.

Q. Is shrinking the same as truncating?

A. No. While truncating frees the storage space for reuse, shrinking releases space to the Operating System.

Q. How to shrink log file in SQL Server automatically?

A. You can shrink log files automatically by setting up an SQL Server Agent Job using a command scheduled at intervals. However, it is discouraged for production environments due to fragmentation risk. Instead, schedule log backups and monitor log growth patterns.

Q. Can I shrink log files daily?

A. Experts do not recommend it. Instead, you can manage log growth with backups and proper autogrowth settings.

Q. How to shrink log file in SQL Server in full recovery model?

A. Follow the given steps to shrink the file in a full recovery model:

Backup the transaction log to avoid data loss.
Use the DBCC SHRINKFILE command to reduce its size safely.

Q. SQL Server shrink log file not releasing space. What should I do?

A. Check for log backup, open transactions, or database isn’t waiting for log truncation. If the SQL server shrinks, the log file is not releasing space.

Q. How to shrink log file in SQL Server using query?

A. Use the given command to shrink the log file using the SQL query after backing up the transaction log.

DBCC SHRINKFILE (logical_log_name , target_size)

Q. What is the query to Shrink log file in SQL Server for all databases?

A. You can loop through sys.databases using dynamic SQL and apply DBCC SHRINKFILE for each log file to shrink log files of all databases at once.