Why will my SQL Transaction log file not auto-grow?

Question

The Issue

I've been running a particularly large query, generating millions of records to be inserted into a table. Each time I run the query I get an error reporting that the transaction log file is full.

I've managed to get a test query to run with a reduced set of results and by using SELECT INTO instead of INSERT into as pre built table. This reduced set of results generated a 20 gb table, 838,978,560 rows.

When trying to INSERT into the pre built table I've also tried using it with and without a Cluster index. Both failed.

Server Settings

The server is running SQL Server 2005 (Full not Express). The dbase being used is set to SIMPLE for recovery and there is space available (around 100 gb) on the drive that the file is sitting on.

The transaction log file setting is for File Growth of 250 mb and to a maximum of 2,097,152 mb.

The log file appears to grow as expected till it gets to 4729 mb.

When the issue first appeared the file grow to a lower value however i've reduced the size of other log files on the same server and this appears to allow this transaction log file grow further by the same amount as the reduction on the other files.

I've now run out of ideas of how to solve this. If anyone has any suggestion or insight into what to do it would be much appreciated.

Answer 1

First, you want to avoid auto-growth whenever possible; auto-growth events are HUGE performance killers. If you have 100GB available why not change the log file size to something like 20GB (just temporarily while you troubleshoot this). My policy has always been to use 90%+ of the disk space allocated for a specific MDF/NDF/LDF file. There's no reason not to.

If you are using SIMPLE recovery SQL Server is supposed manage the task of returning unused space but sometimes SQL Server does not do a great job. Before running your query check the available free log space. You can do this by:

1. right-click the DB > go to Tasks > Shrink > Files.
2. change the type to "Log"

This will help you understand how much unused space you have. You can set "Reorganize pages before releasing unused space > Shrink File" to 0. Moving forward you can also release unused space using CHECKPOINT; this may be something to include as a first step before your query runs.