sys.databases dm_db_log_info (Transact-sql)sys.dm_db_log_info (Transact-SQL)

適用於: 是SQL Server 2016 SP2 與更新版本 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server 2016 SP 2 and later yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

傳回交易記錄檔的虛擬記錄檔(VLF)資訊。Returns virtual log file (VLF) information of the transaction log. 請注意,所有交易記錄檔都會合並在資料表輸出中。Note all transaction log files are combined in the table output. 輸出中的每個資料列都代表交易記錄中的 VLF,並在記錄檔中提供與該 VLF 相關的資訊。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 |預設database_id | NULL | DEFAULT
資料庫的識別碼。Is the ID of the database. database_idint。有效的輸入為資料庫的識別碼、Null 或預設值。database_id is int. Valid inputs are the ID number of a database, NULL, or DEFAULT. 預設值是 NULL。The default is NULL. Null 和 DEFAULT 在目前資料庫的內容中是對等的值。NULL and DEFAULT are equivalent values in the context of current database.

指定 Null 以傳回目前資料庫的 VLF 資訊。Specify NULL to return VLF information of the current database.

可以指定內建函數 DB_IDThe built-in function DB_ID can be specified. 在未DB_ID指定資料庫名稱的情況下使用時,目前資料庫的相容性層級必須是90或更高。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_iddatabase_id intint 資料庫識別碼。Database ID.
file_idfile_id smallintsmallint 交易記錄檔的檔案識別碼。File id of the transaction log.
vlf_begin_offsetvlf_begin_offset Bigintbigint 從交易記錄檔開頭的虛擬記錄檔(VLF)的位移位置。Offset location of the virtual log file (VLF) from the beginning of the transaction log file.
vlf_size_mbvlf_size_mb floatfloat 虛擬記錄檔(VLF)大小,以 MB 為單位,四捨五入為2個小數位數。virtual log file (VLF) size in MB, rounded to 2 decimal places.
vlf_sequence_numbervlf_sequence_number Bigintbigint 建立的順序中的虛擬記錄檔(VLF)序號。virtual log file (VLF) sequence number in the created order. 用來唯一識別記錄檔中的 Vlf。Used to uniquely identify VLFs in log file.
vlf_activevlf_active bitbit 指出虛擬記錄檔(VLF)是否正在使用中。Indicates whether virtual log file (VLF) is in use or not.
0-VLF 不在使用中。0 - VLF is not in use.
1-VLF 為使用中狀態。1 - VLF is active.
vlf_statusvlf_status intint 虛擬記錄檔(VLF)的狀態。Status of the virtual log file (VLF). 可能的值包括Possible values include
0-VLF 為非作用中0 - VLF is inactive
1-VLF 已初始化但未使用1 - VLF is initialized but unused
2-VLF 為使用中狀態。2 - VLF is active.
vlf_parityvlf_parity tinyinttinyint 虛擬記錄檔(VLF)的同位檢查。在內部用來判斷 VLF 內記錄檔的結尾。Parity of virtual log file (VLF).Used internally to determine the end of log within a VLF.
vlf_first_lsnvlf_first_lsn Nvarchar (48)nvarchar(48) 虛擬記錄檔中第一筆記錄的記錄序號(LSN) (VLF)Log sequence number (LSN) of the first log record in the virtual log file (VLF).
vlf_create_lsnvlf_create_lsn Nvarchar (48)nvarchar(48) 建立虛擬記錄檔(VLF)之記錄檔記錄的記錄序號(LSN)Log sequence number (LSN) of the log record that created the virtual log file (VLF).
vlf_encryptor_thumbprintvlf_encryptor_thumbprint Varbinary (20)varbinary(20) 適用物件:SQL Server 2019 (15.x)SQL Server 2019 (15.x)Applies to: SQL Server 2019 (15.x)SQL Server 2019 (15.x)

如果使用透明資料加密加密 VLF,則顯示 VLF 的加密器指紋,否則為 Null。Shows the thumbprint of the encryptor of the VLF if the VLF is encrypted using Transparent Data Encryption, otherwise NULL.

備註Remarks

sys.dm_db_log_info動態管理函數會取代DBCC LOGINFO語句。The sys.dm_db_log_info dynamic management function replaces the DBCC LOGINFO statement.

權限Permissions

需要資料庫VIEW DATABASE STATE中的許可權。Requires the VIEW DATABASE STATE permission in the database.

範例Examples

A.A. 在具有大量 Vlf 的 SQL Server 實例中判斷資料庫Determing databases in a SQL Server instance with high number of VLFs

下列查詢會判斷記錄檔中有超過 100 Vlf 的資料庫,這可能會影響資料庫啟動、還原和復原時間。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 s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
HAVING COUNT(l.database_id) > 100

B.B. 先判斷交易記錄中VLF最後一個位置,再壓縮記錄檔Determing the position of the last VLF in transaction log before shrinking the log file

下列查詢可用來判斷上次使用中 VLF 的位置,然後再于交易記錄檔上執行 shrinkfile,以判斷交易記錄檔是否可以壓縮。The following query can be used to determine the position of the last active VLF before running shrinkfile on transaction log to determine if transaction log can shrink.

USE AdventureWorks2016
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

另請參閱See Also

動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
資料庫相關的動態管理檢視 (Transact-sql) Database Related Dynamic Management Views (Transact-SQL)
dm_db_log_space_usage (Transact-sql) sys.dm_db_log_space_usage (Transact-SQL)
dm_db_log_stats (Transact-sql)sys.dm_db_log_stats (Transact-SQL)