There are two ways you can limit the size of your transaction log files.

1. Using SSMS (SQL Server Management Studio)
2. By writing a manual script.

Below are the steps to limit transaction log files.

Open your Microsoft SQL Server Management Studio

Find the database that you would like to set the transaction log size.

Right-click on the database and select Properties.

Select the Files Tab from select a page area.

Under Database files on the right for LogFile Line, find the Initial Size option and change the size to 100mb or whatever size your need is.

Under the same tab, find┬áthe Autogrowth section and click on the ellipses (…)

Uncheck the option for Enable Autogrowth and Click OK.

Click OK to save the changes.

You can additionally set the maximum log file size in “Autogrowth popup window”

Script to set the Log File:

 

USE [master]
GO
ALTER DATABASE [YOUR_DB_NAME] MODIFY FILE ( NAME = N’YOUR_DB_NAME_Log’, SIZE = 100000KB , FILEGROWTH = 0)
GO

How to Limit Transaction Log file in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *