Article ID: 317375 - View products that this article applies to.
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:
If you are using SQL Server 2005, you receive an error message that resembles the following:
Error: 9002, Severity: 17, State: 2
The log file for database '%.*ls' is full.
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.
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
Additionally, transaction log expansion may occur for one of the following reasons or in one of the following scenarios:
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.
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
AlwaysOn 'AVAILABILITY_REPLICA' applying transaction log records to a secondary database
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.
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:
315512Truncation 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.
(http://support.microsoft.com/kb/315512/ )Considerations for Autogrow and Autoshrink configuration in SQL Server
For more information about how to shrink transaction logs, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/256650/ )How to shrink the SQL Server 7.0 transaction log
272318For more information about SQL Server 6.5 transaction log usage, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/272318/ )Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
(http://support.microsoft.com/kb/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 versionsIn 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:
For more information about the sys.dm_tran_database_transactions DMV, go to the sys.dm_tran_database_transactions (Transact-SQL)
(http://msdn2.microsoft.com/en-us/library/ms186957.aspx)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)
For more information about the sys.dm_exec_requests DMV, go to the sys.dm_exec_requests (Transact-SQL)
Article ID: 317375 - Last Review: January 13, 2014 - Revision: 12.0
Contact us for more help