A transaction log grows unexpectedly or becomes full in SQL Server


Transaction log expansion may occur for one of the following reasons or scenarios.

Note In SQL Server 2005 and later versions, you can review the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view to determine why the transaction log space is not reused and why the transaction log cannot be truncated.

Uncommitted transactions
Explicit transactions remain uncommitted if you do not issue an explicit COMMIT or ROLLBACK command. This occurs most frequently 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 that there is an active transaction in a database at a particular time. For more information about this particular scenario, click the following article numbers to view the articles in the Microsoft Knowledge Base:

295108 Incomplete transaction may hold large number of locks and case blocking

171224 Understanding how the Transact-SQL KILL command works

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 consider 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.

    We recommend the following when you design your application:
    • pen only 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 allows for user interaction inside transactions. This causes the transaction to remain open for a long time, and this 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 after that 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. This results in large log file sizes. For more information about connection pooling, click the following article number to view the article in the Microsoft Knowledge Base:

    164221 How to enable connection pooling in an ODBC application

If the autogrow option is set in Microsoft SQL Server 2005 and later versions, SQL Server 2000, and SQL Server 7.0, transaction log files can expand automatically to the maximum log file size of 2 terabytes (TB) per log file.

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 are triggered by either checkpoints or transaction log backups.

However, in some cases 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 uses up the available disk space and cannot expand any longer:
Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full.
If you are using SQL Server 2005, you receive an error message that resembles the following:
Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
In addition to this error message, SQL Server may mark databases as suspect because of a lack of space for transaction log expansion. For more information about how to recover from this situation, see the "Insufficient Disk Space" topic in SQL Server Books Online.

Additionally, transaction log expansion may occur for one of the following reasons or in one of the following scenarios:
  • 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.
  • The database is participating in an AlwaysOn availability group.

Very large transactions
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 is completed. 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 more information about what to do when you receive this kind of error message, see the "More Information" section in this article. Additionally, it takes a lot of time and SQL Server overhead to roll back large transactions.

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.

When restoring from transaction log backups
This is described in the following Microsoft Knowledge Base article:
232196 Log space used appears to grow after restoring from backup

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 lets you 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 uses 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.

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 the server.

For example, suppose that 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 added 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.

Queries time out before a transaction log finishes the expansion and you receive false 'Log full' error messages
In this situation, although there is sufficient 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 a very small time during which a transaction log tries to automatically expand. However, it cannot expand because the auto-shrink function is running at the same time. 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.
  • The server is slow for various reasons.
  • Disk drives are not fast enough.

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 how to troubleshoot unreplicated transactions, see the "sp_replcounters" and "sp_repldone" topics in SQL Server Books Online.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

306769 FIX: Transaction log of snapshot published database cannot be truncated

240039 FIX: DBCC OPENTRAN does not report replication information

198514 FIX: Restore to new server causes transactions to remain in log

AlwaysOn 'AVAILABILITY_REPLICA' applying transaction log records to a secondary database

In SQL Server 2012 with AlwaysOn Availability Groups enabled, you may see following message in the SQL error log:

Error: 9002, Severity: 17, State: 9.
The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'

AVAILABILITY_REPLICA log_reuse_wait indicates an AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.

There are two scenarios that can lead to log growth in an availability database and the AVAILABILITY_REPLICA’ log_reuse_wait:

Scenario 1: Latency delivering logged changes to secondary

When a transaction is performed at the primary, the logged blocks must be delivered and hardened to the database log file at the secondary. Any delay will prevent truncation of those logged changes in the database at the primary replica.

Scenario 2: Redo Latency

Once hardened to the secondary database log file a dedicated redo thread applies the log records.

If the redo operation is not able to keep up with the transaction log generated, it can potentially lead to log growth. The primary will be unable to truncate the transaction log if the secondary replica redo operation is behind in applying those changes to a corresponding secondary database. If there is more than one secondary, to identify which secondary database is delaying log truncation, compare the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view across the multiple secondaries.

You can use the AlwaysOn Dashboard and sys.dm_hadr_database_replica_states dynamic management views to help monitor the log send queue and redo queue. Some key fields are:

log_send_queue_sizeAmount of log records that have not arrived at the secondary replica
log_send_rateRate at which log records are being sent to the secondary databases
redo_queue_sizeThe amount of log records in the log files of the secondary replica that has not yet been redone, in kilobytes (KB)
redo_rateThe rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second
last_redone_lsnActual log sequence number of the last log record that was redone on the secondary database. last_redone_lsn is always less than last_hardened_lsn
last_received_lsnLog block ID identifying the point up to which all log blocks have been received by the secondary replica that hosts this secondary database. Reflects a log-block ID padded with zeroes. It is not an actual log sequence number.

Note For more information about the sys.dm_hadr_database_replica_states view, see the following TechNet website:


Advanced Information

The transaction log for any database is managed as a set of virtual log files (VLFs). SQL Server determines VLF file sizes internally based on the total size of the log file and the growth increment that is used when the log expands. A log always expands in units of whole VLFs and it can only compress to a VLF boundary. A VLF can exist in one of three states: ACTIVE, RECOVERABLE, and REUSABLE.
  • ACTIVE: The active part of the log begins at the minimum log sequence number (LSN) that represents an active (uncommitted) transaction. The active part of the log ends at the last-written LSN. Any VLFs that contain any part of the active log are considered active VLFs. (Unused space in the physical log is not part of any VLF.)
  • RECOVERABLE: The part of the log that comes before the oldest active transaction is only necessary to maintain a sequence of log backups for recovery.
  • REUSABLE: If you are not maintaining transaction log backups, or if you already backed up the log, SQL Server reuses VLFs before the oldest active transaction.
When SQL Server reaches the end of the physical log file, it starts reusing that space in the physical file by issuing a CIRCLING BACK operation to the beginning of the files. In effect, SQL Server recycles the space in the log file that is no longer necessary for recovery or backup purposes. If a log backup sequence is being maintained, the part of the log before the minimum LSN can't be overwritten until you back up or truncate those log records. After you perform the log backup, SQL Server can circle back to the beginning of the file. After SQL Server circles back to start to write log records earlier in the log file, the reusable part of the log is then between the end of the logical log and active part of the log.

For more information, see the "Transaction Log Physical Architecture" topic in SQL Server Books Online. Additionally, you can see a diagram and discussion of this on page 190 of "Inside SQL Server 7.0" (Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999), and also on pages 182 to 186 of "Inside SQL Server 2000" (Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000).
SQL Server 2000 and SQL Server 7.0 databases have the options to autogrow and autoshrink. You can use these options to help you compress or expand your transaction log.

For more information about how these options can affect the server, click the following article number to view the article in the Microsoft Knowledge Base:

315512 Considerations for Autogrow and Autoshrink configuration in SQL Server

Truncation of the transaction log file differs from the compression of the transaction log file. When SQL Server truncates a transaction log file, this means that the contents of that file (for example, the committed transactions) are deleted. However, when you are viewing the size of the file from a disk space perspective (for example, in Windows Explorer or by using the dir command), the size remains unchanged. However, the space inside the .ldf file can now be reused by new transactions. Only when SQL Server shrinks the size of the transaction log file do you actually see a change in the physical size of the log file.

For more information about how to shrink transaction logs, click the following article numbers to view the articles in the Microsoft Knowledge Base:

256650 How to shrink the SQL Server 7.0 transaction log

272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE

For more information about SQL Server 6.5 transaction log usage, click the following article number to view the article in the Microsoft Knowledge Base:

110139 Causes of SQL transaction log filling up

How to locate queries that consume a large amount of log space in SQL Server 2005 and later versions

In SQL Server 2005 and later versions, you can use the sys.dm_tran_database_transactions dynamic management view (DMV) to locate queries that consume large amounts of log space. The following columns in the sys.dm_tran_database_transactions DMV can be useful:
  • database_transaction_log_bytes_used
  • database_transaction_log_bytes_used_system
  • database_transaction_log_bytes_reserved
  • database_transaction_log_bytes_reserved_system
  • database_transaction_log_record_count
You can query the sql_handle column of the sys.dm_exec_requests DMV to obtain the actual statement text that consumes large amounts of log space. You can do this by joining the sys.dm_tran_database_transactions DMV and the sys.dm_tran_session_transactions DMV on the transaction_id column, and then adding an additional join with sys.dm_exec_requests on the session_id column.

For more information about the sys.dm_tran_database_transactions DMV, go to the sys.dm_tran_database_transactions (Transact-SQL) Microsoft Developer Network (MSDN) website.

For more information about the sys.dm_tran_session_transactions DMV, go to the sys.dm_tran_session_transactions (Transact-SQL) MSDN website.

For more information about the sys.dm_exec_requests DMV, go to the sys.dm_exec_requests (Transact-SQL) MSDN website.

Αναγνωριστικό άρθρου: 317375 - Τελευταία αναθεώρηση: 13 Ιαν 2014 - Αναθεώρηση: 1