Beeravolu Technologies

We provide tech review and share knowledge in the form of blog post

Database

How to shrink log file in SQL Server

By | Published

SQL Server DBAs sometimes require to shrink the Transaction log file. Although, if the full and log backup is configured correctly at regular intervals, then the chances are very less that the transaction log for the database gets full.

When the database is in the Full recovery model, SQL Server truncates the transaction logs after every transaction log backup, and the log file size does not increase continuously. To stop log files from growing too large in SQL Server, you must configure regular transaction log backups.

However, if you find the database log file in big size, you can perform a shrink operation on the transaction log file to release unused space.

To reduce the log file size in SQL Server, right-click on the database and Select Tasks > Shrink > Files option as shown in the below screenshot.

Shrink log file in SQL
Shrink log file in SQL

A new window will appear, Choose the file type as LOG as shown below.

Shrink log file in SQL Server
Shrink log file in SQL Server

Once you select the Log as file type, you will see the log file details like log filename, location, currently allocated space, and available free space as shown below. Click the OK button to start the shrink operation.

Shrink Transaction log file in SQL
Shrink Transaction log file in SQL

Once the shrinking operation is complete, the above window will close and you can check the size of the log files to confirm if the SQL Server has cleared the log file. It is also possible in some cases, that SQL Server shrinks the log file not releasing space because of some uncommitted transactions in the log file. If this happens, take a transaction log backup and execute the steps again to shrink the log file.

You can shrink the log file using TSQL statement as well.

USE [SQL_KDB]

GO

DBCC SHRINKFILE (N’ SQL_KDB_Log’ , 0, TRUNCATEONLY)

GO

Conclusion

In this article, I have tried to explain the steps required to shrink database transaction log file in SQL Server. Steps are mentioned along with screenshots to understand easily. I have also shared the query to reduce the log file size using T-SQL. I hope this article is helpful to others.

Latest posts