Knowledge Base

Database log files may continue to grow even though transaction logs are backed up

Category: How do I?
Date: 05 Jun 2009
Product: SQL Backup
Database log files may continue to grow, even though frequent log backups with truncation are performed. If this occurs, the log files must be shrunk manually.

In ordinary circumstances, periodic log backups of databases that use the FULL recovery model will prevent the database log file from growing out of control. Once a log backup is performed, the backed-up transactions are marked as free in the database log and the space can be reclaimed by new transactions. If you think of a database log file as a container full of water, a log backup would be a glass filled from the container so another glass of water would fit.

The trouble occurs when the number of transactions being recorded in the log exceed the amount of reclaimable space freed by a log backup. This causes the database log file to grow.

For instance, if the log is backed up every day, but on a particular day, millions of updates were performed on the database and the log file is unusually large. Once a transaction log backup is performed there is a lot of free space in the container, but the container itself does not shrink.

Shrinking the log file manually will shrink the log file to a size that will hold all of the current transactions (plus a few percent more), but this must be done using a SQL query of management utility such as Management Studio. SQL Backup will not do this for you.

There are two methods to shrink a log file. From a query window, you may use DBCC SHRINKFILE (see the SQL Server Books online for usage instructions).

SQL 2000 Enterprise Manager can also shrink a log file from its' GUI using the taskpad view. Highlight the database to be shrunk and then select Taskpad from the view menu of Enterprise Manager. Click the dropdown arrow in the 'space allocated' category and select 'shrink database', then choose the logical name of the log file from the dropdown.

In SQL Server 2005 Management Studio, you may shrink a log file by right-clicking the database, selecting Tasks, then Shrink, then Files.