A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server (317375)

SUMMARY

In SQL Server 7.0, in SQL Server 2000, and in SQL Server 2005, with the autogrow setting, transaction log files can expand automatically.

Typically, the size of the transaction log file stabilizes when it can hold the maximum number of transactions that can occur between transaction log truncations that either checkpoints or transaction log backups trigger.

However, in some situations the transaction log may become very large and run out of space or become full. Typically, you receive the following error message when a transaction log file takes up the available disk space and cannot expand any more: If you are using SQL Server 2005, you receive an error message that is similar to the following: In addition to this error message, SQL Server may mark databases suspect because of a lack of space for transaction log expansion. For additional information about how to recover from this situation, see the "Insufficient Disk Space" topic in SQL Server Books Online.

Additionally, transaction log expansion may result in the following situations:

• A very large transaction log file.
• Transactions may fail and may start to roll back.
• Transactions may take a long time to complete.
• Performance issues may occur.
• Blocking may occur.

Causes

Transaction log expansion may occur because of the following reasons or scenarios:

• Uncommitted Transactions
• Extremely Large Transactions
• Operations: DBCC DBREINDEX and CREATE INDEX
• While Restoring from Transaction Log Backups
• Client Applications Do Not Process All Results
• Queries Time Out Before a Transaction Log Completes the Expansion and You Receive False 'Log Full' Error Messages
• Unreplicated Transactions

Uncommitted Transactions

Explicit transactions remain uncommitted if you do not issue an explicit COMMIT or ROLLBACK command. This most frequently occurs when an application issues a CANCEL or a Transact SQL KILL command without a corresponding ROLLBACK command. The transaction cancellation occurs, but it does not roll back; therefore, SQL Server cannot truncate every transaction that occurs after this because the aborted transaction is still open. You can use the DBCC OPENTRAN Transact-SQL reference to verify if there is an active transaction in a database at a particular time. For additional information about this particular scenario, click the article number below to view the article in the Microsoft Knowledge Base: Additionally, see the "DBCC OPENTRAN" topic in SQL Server Books Online.

Scenarios that may result in uncommitted transactions:

• An application design that assumes that all errors cause rollbacks.
• An application design that does not completely take into account SQL Server behavior when it rolls back to named transactions or specially-nested named transactions. If you try to roll back to an inner-named transaction, you receive the following error message:
  Server: Msg 6401, Level 16, State 1, Line 13 Cannot roll back InnerTran. No transaction or savepoint of that name was found.
  After SQL Server generates the error message, it continues to the next statement. This is by design. For more information, see the "Nested Transactions" or "Inside SQL Server" topic in SQL Server Books Online.
  
  Microsoft recommends the following when you design your application:
  • Only open one transaction unit (consider the possibility that another process may call yours).
  • Check @@TRANCOUNT before you issue a COMMIT, a ROLLBACK, a RETURN, or a similar command or statement.
  • Write your code with the assumption that another @@TRANCOUNT might "nest" yours and plan for the outer @@TRANCOUNT to be rolled back when an error occurs.
  • Review savepoint and mark options for transactions. (These do not release locks!)
  • Perform complete testing.
• An application that permits user interaction inside transactions. This causes the transaction to remain open for a long time, which causes blocking and transaction log growth because the open transaction cannot be truncated and new transactions are added to the log after the open transaction.
• An application that does not check @@TRANCOUNT to verify that there are no open transactions.
• Network or other errors that close the client application connection to SQL Server without informing it.
• Connection pooling. After worker threads are created, SQL Server reuses them if they are not servicing a connection. If a user connection starts a transaction and disconnects before committing or rolling back the transaction, and a connection thereafter reuses the same thread, the previous transaction still stays open. This situation results in locks that stay open from the previous transaction and prevents the truncation of the committed transactions in the log, which results in large log file sizes.For additional information about connection pooling, click the article number below to view the article in the Microsoft Knowledge Base:

  164221 INFO: How to Enable Connection Pooling in an ODBC Application

back to the top

Extremely Large Transaction

Log records in the transaction log files are truncated on a transaction-by-transaction basis. If the transaction scope is large, that transaction and any transactions started after it are not removed from the transaction log unless it completes. This can result in large log files. If the transaction is large enough, the log file might use up the available disk space and cause the "transaction log full" type of error message such as Error 9002. For additional information about what to do when you receive this type of error message is provided in the "More Information" section in this article. Additionally, it takes a lot of time and SQL Server overhead to roll back large transactions.

back to the top

Operations: DBCC DBREINDEX and CREATE INDEX

Because of the changes in the recovery model in SQL Server 2000, when you use the Full recovery mode and you run DBCC DBREINDEX, the transaction log may expand significantly more compared to that of SQL Server 7.0 in an equivalent recovery mode with the use of SELECT INTO or BULK COPY and with "Trunc. Log on chkpt." off.

Although the size of the transaction log after the DBREINDEX operation might be an issue, this approach provides better log restore performance.

back to the top

While Restoring from Transaction Log Backups

This is described in the following Microsoft Knowledge Base article:
If you set SQL Server 2000 to use Bulk-Logged mode and you issue a BULK COPY or SELECT INTO statement, every changed extent is marked and then backed up when you back up the transaction log. Although this permits you to back up transaction logs and recover from failures even after you perform bulk operations, this adds to the size of the transaction logs. SQL Server 7.0 does not include this feature. SQL Server 7.0 only records which extents are changed, but it does not record the actual extents. Therefore, the logging takes up significantly more space in SQL Server 2000 than in SQL Server 7.0 in Bulk-Log mode but not as much as it does in Full mode.

back to the top

Client Applications Do Not Process All Results

If you issue a query to SQL Server and you do not handle the results immediately, you may be holding locks and reducing concurrency on your server.

For example, suppose you issue a query that requires rows from two pages to populate your result set. SQL Server parses, compiles, and runs the query. This means that shared locks are placed on the two pages that contain the rows that you must have to satisfy your query. Additionally, suppose that not all rows fit onto one SQL Server TDS packet (the method by which the server communicates with the client). TDS packets are filled and sent to the client. If all rows from the first page fit on the TDS packet, SQL Server releases the shared lock on that page but leaves a shared lock on the second page. SQL Server then waits for the client to request more data (you can do this by using DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults, or FetchLast/FetchFirst for example).

This means that the shared lock is held until the client requests the rest of the data. Other processes that request data from the second page may be blocked.

back to the top

Queries Time Out Before a Transaction Log Completes the Expansion and You Receive False 'Log Full' Error Messages

In this situation, although there is enough disk space, you still receive an "out of space" error message.

This situation varies for SQL Server 7.0 and SQL Server 2000.

A query can cause the transaction log to automatically expand if the transaction log is almost full. This may take additional time, and a query may be stopped or may exceed its time-out period because of this. SQL Server 7.0 returns error 9002 in this situation. This issue does not apply to SQL Server 2000.

In SQL Server 2000, if you have the auto-shrink option turned on for a database, there is an extremely small time during which a transaction log tries to automatically expand, but it cannot because the auto-shrink function is running simultaneously. This may also cause false instances of error 9002.

Typically, the automatic expansion of transaction log files occurs quickly. However, in the following situations, it may take longer than usual:

• Growth increments are too small.
• Server is slow for various reasons.
• Disk drives are not fast enough.

back to the top

Unreplicated Transactions

The transaction log size of the publisher database can expand if you are using replication. Transactions that affect the objects that are replicated are marked as "For Replication." These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases. You can use the DBCC OPENTRAN Transact-SQL reference to identify the oldest non-replicated transaction.

For more information about troubleshooting unreplicated transactions, see the "sp_replcounters" and "sp_repldone" topics in SQL Server Books Online.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: back to the top