sys.dm_db_log_info (Transact-SQL)
適用対象: 
SQL Server 2016 (13.x) SP 2 以降 Azure SQL Database Azure SQL Managed Instance
トランザクション ログの仮想ログ ファイル (VLF) 情報を返します。 すべてのトランザクション ログ ファイルがテーブル出力に結合されることに注意してください。 出力の各行は、トランザクション ログの VLF を表し、ログ内のその VLF に関連する情報を提供します。
構文
sys.dm_db_log_info ( database_id )
引数
database_id | NULL | DEFAULT
データベースの ID です。 database_id は int です。有効な入力は、データベースの ID 番号、NULL、または DEFAULT です。 の既定値は NULL です。 NULL と DEFAULT は、現在のデータベースのコンテキストで同等の値です。
現在のデータベースの VLF 情報を返すには、NULL を指定します。
組み込み関数 DB_ID を指定できます。 データベース名を指定しないで DB_ID を使用する場合、現在のデータベースの互換性レベルが 90 以上である必要があります。
返されるテーブル
| 列名 | データ型 | 説明 |
|---|---|---|
| database_id | int | データベース ID。 Azure SQL Database では、値は 1 つのデータベースまたは Elastic Pool 内で一意ですが、論理サーバー内では一意ではありません。 |
| file_id | smallint | トランザクション ログのファイル ID。 |
| vlf_begin_offset | bigint | トランザクション ログ ファイルの先頭からの仮想ログ ファイル (VLF) のオフセット位置。 |
| vlf_size_mb | float | 仮想ログ ファイル (VLF) のサイズ (MB 単位)。小数点以下 2 桁に丸められます。 |
| vlf_sequence_number | bigint | 作成された順序での仮想ログ ファイル (VLF) シーケンス番号。 ログ ファイル内の VTF を一意に識別するために使用されます。 |
| vlf_active | bit | 仮想ログ ファイル (VLF) が使用中かどうかを示します。 0 - VLF は使用されていません。 1 - VLF はアクティブです。 |
| vlf_status | int | 仮想ログ ファイル (VLF) の状態。 指定できる値は、次のとおりです。 0 - VLF は非アクティブです 1 - VLF は初期化されているが未使用です 2 - VLF はアクティブです。 |
| vlf_parity | tinyint | 仮想ログ ファイル (VLF) のパリティ。 VLF 内のログの末尾を特定するために内部的に使用されます。 |
| vlf_first_lsn | nvarchar(48) | 仮想ログ ファイル (VLF) の最初のログ レコードのログ シーケンス番号 (LSN)。 |
| vlf_create_lsn | nvarchar(48) | 仮想ログ ファイル (VLF) を作成したログ レコードのログ シーケンス番号 (LSN)。 |
| vlf_encryptor_thumbprint | varbinary(20) | 適用対象: SQL Server 2019 (15.x) 以降 VLF が Transparent Data Encryption を使用して暗号化されている場合は、VLF の暗号化子のサムプリントを表示します。それ以外の場合は NULL です。 |
解説
sys.dm_db_log_info 動的管理機能は DBCC LOGINFO ステートメントを置き換えます。
拡張イベントに基づいて作成される VTF の数の数式については、「SQL Server トランザクション ログのアーキテクチャと管理ガイド」を参照してください。 この数式は、SQL Server 2022 (16.x) から若干変更されました。
アクセス許可
データベースでの VIEW SERVER STATE 権限が必要です。
SQL Server 2022 以降でのアクセス許可
データベースに対する VIEW DATABASE PERFORMANCE STATE アクセス許可が必要です。
例
A. VTF の数が多い SQL Server インスタンス内のデータベースを特定する
次のクエリでは、ログ ファイルに 100 を超える VTF が含まれるデータベースを特定します。これは、データベースの起動、復元、復旧時間に影響を与える可能性があります。
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. ログ ファイルを圧縮する前に、トランザクション ログの最後の VLF の位置を確認する
次のクエリを使用して、トランザクション ログで SHRINK FILE を実行する前の最後のアクティブな VLF の位置を確認し、トランザクション ログが圧縮できるかどうかを判断できます。
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