संपादित करें

इसके माध्यम से साझा किया गया

sys.dm_db_log_info (Transact-SQL)

  • आलेख

Applies to: SQL Server 2016 (13.x) SP 2 and later Azure SQL Database Azure SQL Managed Instance

Returns virtual log file (VLF) information of the transaction log. Note all transaction log files are combined in the table output. Each row in the output represents a VLF in the transaction log and provides information relevant to that VLF in the log.

Syntax

sys.dm_db_log_info ( database_id )

Arguments

database_id | NULL | DEFAULT

Is the ID of the database. database_id is int. Valid inputs are the ID number of a database, NULL, or DEFAULT. The default is NULL. NULL and DEFAULT are equivalent values in the context of current database.

Specify NULL to return VLF information of the current database.

The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.

Table Returned

Column name Data type Description
database_id int Database ID.

In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a logical server.
file_id smallint The file ID of the transaction log.
vlf_begin_offset bigint Offset location of the virtual log file (VLF) from the beginning of the transaction log file.
vlf_size_mb float virtual log file (VLF) size in MB, rounded to two decimal places.
vlf_sequence_number bigint virtual log file (VLF) sequence number in the created order. Used to uniquely identify VLFs in log file.
vlf_active bit Indicates whether virtual log file (VLF) is in use or not.
0 - VLF is not in use.
1 - VLF is active.
vlf_status int Status of the virtual log file (VLF). Possible values include
0 - VLF is inactive
1 - VLF is initialized but unused
2 - VLF is active.
vlf_parity tinyint Parity of virtual log file (VLF). Used internally to determine the end of log within a VLF.
vlf_first_lsn nvarchar(48) Log sequence number (LSN) of the first log record in the virtual log file (VLF).
vlf_create_lsn nvarchar(48) Log sequence number (LSN) of the log record that created the virtual log file (VLF).
vlf_encryptor_thumbprint varbinary(20) Applies to: SQL Server 2019 (15.x) and later

Shows the thumbprint of the encryptor of the VLF if the VLF is encrypted using Transparent Data Encryption, otherwise NULL.

Remarks

The sys.dm_db_log_info dynamic management function replaces the DBCC LOGINFO statement.

The formula for how many VLFs are created based on a growth event is detailed in the SQL Server Transaction Log Architecture and Management Guide. This formula changed slightly starting in SQL Server 2022 (16.x).

Permissions

Requires the VIEW SERVER STATE permission in the database.

Permissions for SQL Server 2022 and later

Requires VIEW DATABASE PERFORMANCE STATE permission on the database.

Examples

A. Determine databases in a SQL Server instance with high number of VLFs

The following query determines the databases with more than 100 VLFs in the log files, which can affect the database startup, restore, and recovery time.

SELECT [name], COUNT(l.database_id) AS 'vlf_count' 
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_info(s.database_id) AS l
GROUP BY [name]
HAVING COUNT(l.database_id) > 100;

B. Determine the position of the last VLF in transaction log before shrinking the log file

The following query can be used to determine the position of the last active VLF before running SHRINK FILE on transaction log to determine if transaction log can shrink.

USE AdventureWorks2022;
GO

;WITH cte_vlf AS (
SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb
    FROM sys.dm_db_log_info(DEFAULT)),
cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active,
    (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active,
    (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active,
    (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active,
    (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active
    FROM cte_vlf
    GROUP BY [Database Name])
SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active,
((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log,
((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct,
((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log
FROM cte_vlf_cnt;
GO

Related content