Last VLF status of the current database

When you're working with SQL Server, understanding your transaction log files (VLFs) is crucial for database performance and management. This quick T-SQL snippet helps you get a snapshot of the most recent Virtual Log File (VLF) for your current database.

Understanding Your Latest VLF

This SQL query leverages the sys.dm_db_log_info dynamic management function, which provides detailed information about the virtual log files of the transaction log. By ordering the results by vlf_sequence_number in descending order and selecting only the top 1, we can quickly pinpoint the most recently created VLF.

SELECT TOP (1) DB_NAME(li.database_id) AS [Database Name]
 ,li.[file_id]
 ,li.vlf_size_mb
 ,li.vlf_sequence_number
 ,li.vlf_active
 ,li.vlf_status
FROM sys.dm_db_log_info(DB_ID()) AS li
ORDER BY vlf_sequence_number DESC
OPTION (RECOMPILE);

What the Columns Mean:

• Database Name: The name of the database.
• file_id: The ID of the log file.
• vlf_size_mb: The size of the VLF in megabytes.
• vlf_sequence_number: The sequence number of the VLF, indicating its order of creation.
• vlf_active: Indicates if the VLF is currently active (1 = active, 0 = inactive).
• vlf_status: The status of the VLF (e.g., 2 for active, 0 for reusable).

This query is a handy tool for database administrators looking to quickly assess the state of their transaction log and identify potential issues related to VLF fragmentation.