通过

sys.dm_db_log_info (Transact-SQL)

  • 项目

适用于： SQL Server 2016 (13.x) SP 2 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

返回事务日志的虚拟日志文件 (VLF) 信息。 请注意，所有事务日志文件都合并在表输出中。 输出中的每行均表示事务日志中的 VLF，并提供与日志中该 VLF 相关的信息。

语法

sys.dm_db_log_info ( database_id )

参数

database_id | NULL | DEFAULT

数据库的 ID。 database_id 是 int。有效的输入包括数据库的 ID 号、NULL 或 DEFAULT。 默认值为 NULL。 NULL 和 DEFAULT 是当前数据库的上下文中的等效值。

指定 NULL 可返回当前数据库的 VLF 信息。

你可以指定内置函数 DB_ID。 如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90 或更高。

返回的表

列名称 数据类型 描述
database_id int 数据库 ID。

在 Azure SQL 数据库中，这些值在单一数据库或弹性池中是唯一的，但在逻辑服务器中不是唯一的。
file_id smallint 事务日志的文件 ID。
vlf_begin_offset bigint 虚拟日志文件 (VLF) 相对于事务日志文件开头的偏移位置。
vlf_size_mb float 虚拟日志文件 (VLF)大小（以 MB 为单位），舍入为两位小数。
vlf_sequence_number bigint 虚拟日志文件 (VLF) 序列号（按已创建的顺序）。 用于唯一标识日志文件中的 VLF。
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 使用透明数据加密进行加密，则显示 VLF 加密器的指纹，否则显示 NULL。

注解

sys.dm_db_log_info 动态管理函数将替换 DBCC LOGINFO 语句。

SQL Server 事务日志体系结构和管理指南中详细介绍了根据增长事件创建多少个 VLF 的公式。 从 SQL Server 2022 (16.x) 开始，此公式略有更改。

权限

需要数据库中的 VIEW SERVER STATE 权限。

SQL Server 2022 及更高版本的权限

需要对数据库拥有 VIEW DATABASE PERFORMANCE STATE 权限。

示例

A. 确定 SQL Server 实例中具有大量 VDF 的数据库

以下查询确定日志文件中 VLF 超过 100 个的数据库，这可能会影响数据库的启动、还原和恢复时间。

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

相关内容