Article ID: 317375 - View products that this article applies to.
Expand all | Collapse all

On This Page

Summary

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.

More information

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

Very large transactions

Operations: DBCC DBREINDEX and CREATE INDEX

When restoring from transaction log backups

Client applications do not process all results

Queries time out before a transaction log finishes the expansion and you receive false 'Log full' error messages

Unreplicated transactions

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

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.

Properties

Article ID: 317375 - Last Review: January 13, 2014 - Revision: 12.0
Applies to
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbsqlsetup kbinfo KB317375

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com