SQL Server Transaction logs are growing and consuming all available disk space in a day

Products

• IT Management Suite

Issue/Introduction

My SQL server transaction logs are growing each day to the point where they overrun available disk space.

Cause

Checked EvtQFast and EvtQueue thread settings, and adjusted the Throttle delays as found in articles 2344 and 17079. The transaction logs were getting still getting a lot of pressure. Used the following Performance Monitor counters:

• SQLServer:Buffer Manager: Buffer Cache Hit Ratio
• SQLServer:Buffer Manager: Checkpoint pages/sec
• SQLServer:Buffer Manager: Page writes/sec
• SQLServer:Databases: Log Bytes Flushed/sec (for both the Altiris and TempDB databases)
• SQLServer:Databases: Percent Log Used (for both the Altiris and TempDB databases)
• SQLServer:Databases: Log Growths (for both the Altiris and TempDB databases) 

Since Altiris recommends the database be maintained in Simple Recovery Model, one of the tenants that this model essentially tells the database to truncate the log on checkpoint. The TempDB used extensively by Altiris is also a database that uses the same recovery model. During the checkpoint procedure SQL constantly removes the transactions that have been committed inside the simple recovery model database and leaves room in the empty log for new transactions.

This process of checkpointing is considered a low priority operation, and is automatically pushed back in priority on SQL servers that have a lot of load or pressure. When a checkpoint does not occur, the stale transactions remain in the transaction log. When there is a large number of stale transactions in the log, the checkpoint procedure can choose not to remove them becasue the I/O cost of doing so would adversely affect other, higher priority I/O needs.

When checkpoint is no longer clearing transactions, the Percent Log Used is observed to stay between 90 and 99 percent, and only drops when a Log Growth event occurs.

Resolution

Upgrade database server and database to SQL 2005 SP2, which has a better and more efficient checkpoint process.

Applies To

Notification Server 6.0.6074 R6
SQL* 2000 on separate dedicated server.