If the autogrow option is set in Microsoft SQL Server 2005, SQL Server 2000, and SQL Server 7.0, 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 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:
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,
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
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 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.
Transaction log expansion may occur for one of the following reasons or scenarios. Note
In SQL Server 2005, you can review the log_reuse_wait
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
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
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:
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:
How to shrink the SQL Server 7.0 transaction log
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:
Causes of SQL transaction log filling up
How to locate queries that consume a large amount of log space in SQL Server 2005
In SQL Server 2005, 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:
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)
For more information about the sys.dm_exec_requests DMV, go to the sys.dm_exec_requests (Transact-SQL)
Article ID: 317375 - Last Review: July 23, 2012 - Revision: 10.0
- 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
|kbsqlsetup kbinfo KB317375|