sys.dm_db_log_stats (Transact-SQL)

Artikel
02/27/2023

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

Returns summary level attributes and information on transaction log files of databases. Use this information for monitoring and diagnostics of transaction log health.

Transact-SQL syntax conventions

Syntax

 sys.dm_db_log_stats ( 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.
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.

Tables 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.
recovery_model	nvarchar(60)	Recovery model of the database. Possible values include: SIMPLE BULK_LOGGED FULL
log_min_lsn	nvarchar(24)	Current start log sequence number (LSN) in the transaction log.
log_end_lsn	nvarchar(24)	log sequence number (LSN) of the last log record in the transaction log.
current_vlf_sequence_number	bigint	Current virtual log file (VLF) sequence number at the time of execution.
current_vlf_size_mb	float	Current virtual log file (VLF) size in MB.
total_vlf_count	bigint	Total number of virtual log files (VLFs) in the transaction log.
total_log_size_mb	float	Total transaction log size in MB.
active_vlf_count	bigint	Total number of active virtual log files (VLFs) in the transaction log.
active_log_size_mb	float	Total active transaction log size in MB.
log_truncation_holdup_reason	nvarchar(60)	Log truncation holdup reason. The value is same as `log_reuse_wait_desc` column of `sys.databases`. (For more detailed explanations of these values, see The Transaction Log). Possible values include: NOTHING CHECKPOINT LOG_BACKUP ACTIVE_BACKUP_OR_RESTORE ACTIVE_TRANSACTION DATABASE_MIRRORING REPLICATION DATABASE_SNAPSHOT_CREATION LOG_SCAN AVAILABILITY_REPLICA OLDEST_PAGE XTP_CHECKPOINT OTHER TRANSIENT
log_backup_time	datetime	Last transaction log backup time.
log_backup_lsn	nvarchar(24)	Last transaction log backup log sequence number (LSN).
log_since_last_log_backup_mb	float	Log size in MB since last transaction log backup log sequence number (LSN).
log_checkpoint_lsn	nvarchar(24)	Last checkpoint log sequence number (LSN).
log_since_last_checkpoint_mb	float	Log size in MB since last checkpoint log sequence number (LSN).
log_recovery_lsn	nvarchar(24)	Recovery log sequence number (LSN) of the database. If `log_recovery_lsn` occurs before the checkpoint LSN, `log_recovery_lsn` is the oldest active transaction LSN, otherwise `log_recovery_lsn` is the checkpoint LSN.
log_recovery_size_mb	float	Log size in MB since log recovery log sequence number (LSN).
recovery_vlf_count	bigint	Total number of virtual log files (VLFs) to be recovered, if there was failover or server restart.

Remarks

When running sys.dm_db_log_stats against a database that is participating in an Availability Group as a secondary replica, only a subset of the fields described above will be returned. Currently, only database_id, recovery_model, and log_backup_time will be returned when run against a secondary database.

Permissions

Requires the VIEW SERVER STATE permission in the database.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

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

The following query returns the databases with more than 100 VLFs in the log files. Large numbers of VLFs can affect the database startup, restore, and recovery time.

SELECT name AS 'Database Name', total_vlf_count AS 'VLF count' 
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id) 
WHERE total_vlf_count  > 100;

B. Determining databases in a SQL Server instance with transaction log backups older than 4 hours

The following query determines the last log backup times for the databases in the instance.

SELECT name AS 'Database Name', log_backup_time AS 'last log backup time' 
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id);

Del via