Microsoft SQL Server How To Set Maximum Log File Size to Free Up Disk Space

Microsoft SQL Server How To Set Maximum Log File Size to Free Up Disk Space

The log file is used for transactional purposes, even if the database is in SIMPLE RECOVERY MODE. If the LOG size is truly growing beyond 500 MB, then someone is running a query or stored procedure that is requiring that space.

For example, perhaps you are creating indexes against your reporting tables. That will be done inside a transaction so that the changes can be rolled back in case of an error. However, the used space will be released afterward for other transactions once complete.

So, if the log size is starting at 1MB and increasing to say, 700MB, then something is being done that requires that space. If you lock the size to 500MB, you will eventually receive a “log file out of space” error.

But if you really want to fix it at 500MB, you can do the following: (I’m using SQL Server 2005)

1. Launch Microsoft SQL Server Management Studio

2. Locate your database and right-click on it. Select Properties.

3. Click on Files section

4. Locate the LOG FILE line.

5. Change the Initial Size to: 500

6. Locate the Autogrowth section and click on the ellipse (…)

7. Uncheck “Enable Autogrowth”. Click OK.

8. Click OK to make the change.

Note: You can also set a maximum log file size in the “autogrowth section”.

Alternatively, you can use the following script to make the change. Replace DATABASENAME with the appropriate value. Also change the Log File Name if required.

USE [master]GOALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N’DATABASENAME_Log’, SIZE = 512000KB , FILEGROWTH = 0)GO

Reference: http://stackoverflow.com/questions/480897/how-can-i-manage-sql-server-log-size