Efficient Query To Reduce Transaction Log Size

Jump To Right Section Show

Hello Reader! In this post, we want to discuss a nice function to reduce transaction log size in SQL Server. Before we get started, if you want to know about split with any delimited string, please go through the following article: How To Optimize SQL Queries.

What is a transaction log?

A transaction log is a file – an integral part of every SQL Server database. It contains log records produced during the logging process in a SQL Server database. The transaction log is the most important component of a SQL Server database when it comes to disaster recovery – however, it must be uncorrupted. After each database modification – transaction occurrence, a log record is written to the transaction log. All the changes are written sequentially.

Transaction Log File Size

When a user database is created, the initial size and the auto-growth settings of the SQL Server Transaction Log file will replicate the initial size and auto-growth settings of the model system database, also known as the Template database. By default, the SQL Transaction Log file initial size of a newly created database is 8MB, with the auto-growth amount of 64MB.

Why reduce Transaction Log?

During SQL Server work, the transaction log grows if any database changes occur. Regular management of the size of the transaction log is necessary to prevent the transaction log from becoming full. Log truncation or clear SQL Server transaction log is required to keep the log from filling up. The truncation process deletes inactive virtual log files from the logical transaction log, freeing space to be reused by the physical transaction log. The transaction log would eventually fill all the disk space allocated to its physical log files if it is never truncated.

Query to reduce Transaction Log size


USE Document_Movement
GO

-- Show Previous Database Status
SELECT file_id, name as File_Name, type_desc as File_Type, physical_name as Physical_Location, (size * 8.0 / 1024) as Size_MB, (max_size * 8.0 / 1024) AS MaxSize_MB
FROM sys.database_files;

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE Document_Movement
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB. (Logical Log file Name 'Overtime_Log')
DBCC SHRINKFILE (Overtime_Log, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE Document_Movement
SET RECOVERY FULL;
GO

-- Show Current Database Status after Shrink
SELECT file_id, name as File_Name, type_desc as File_Type, physical_name as Physical_Location, (size * 8.0 / 1024) as Size_MB, (max_size * 8.0 / 1024) AS MaxSize_MB 
FROM sys.database_files;

USE Document_Movement

-- Show Previous Database Status

SELECT file_id, name as File_Name, type_desc as File_Type, physical_name as Physical_Location, (size * 8.0 / 1024) as Size_MB, (max_size * 8.0 / 1024) AS MaxSize_MB

FROM sys.database_files;

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE Document_Movement

SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB. (Logical Log file Name 'Overtime_Log')

DBCC SHRINKFILE (Overtime_Log, 1);

-- Reset the database recovery model.

ALTER DATABASE Document_Movement

SET RECOVERY FULL;

-- Show Current Database Status after Shrink

SELECT file_id, name as File_Name, type_desc as File_Type, physical_name as Physical_Location, (size * 8.0 / 1024) as Size_MB, (max_size * 8.0 / 1024) AS MaxSize_MB

FROM sys.database_files;