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 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:
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)