How Can I Manage SQL Server Log Size

Question

I'm trying to manage the size of a SQL Server 2008 log file. I have a reporting database that is loaded once a day. The Simple recovery model is the best fit as there are no transactions other than the morning load, and I can re-create those records. My goals are to have the transaction log at a fixed size, large enough that it doesn't have to allocate new space during the load.

My problem is that the log keeps growing. I know that the log is supposed to be marked for re-use, but why does the log keep growing? The log_reuse_wait_desc shows "Nothing".

I can shrink the log file to 1 MB, so I know there are no transactions in it. I can set up a job to do this, but I would prefer to leave the log at 500MB, which is enough to handle the daily loads. If I do this, the log grows.

How can I keep the log at a consistent size?

Please note: Shrinking the log file does not address this issue. I know how to do that. I'm trying to find a way for space in the transaction log to be re-used.

Answer 1

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]
GO
ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)
GO

---

Answer 2

create a maintenance job that backups the DB and shrinks the log

Answer 3

The DBCC Shrinkfile command allows you to specify a target size:

DBCC SHRINKFILE (DataFile1, 7)

Shrinks the file DataFile1 to 7MB

Document at: MSDN