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 – 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. The 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
Reduce transaction log size in SQL Server

Leave a Comment