Check

VLF

SELECT

file_id,

vlf_begin_offset / 1024.0 / 1024.0 AS vlf_begin_mb,

vlf_size_mb,

vlf_active,

vlf_status,

vlf_sequence_number

FROM sys.dm_db_log_info(DB_ID(N'myDatabase'))

ORDER BY file_id, vlf_begin_offset;

GO

SELECT

COUNT(*) AS total_vlfs,

SUM(CASE WHEN vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlfs,

SUM(CASE WHEN vlf_active = 0 THEN 1 ELSE 0 END) AS inactive_vlfs,

MAX(CASE

         WHEN vlf_active = 1

         THEN vlf_begin_offset / 1024.0 / 1024.0

     END) AS furthest_active_vlf_mb

FROM sys.dm_db_log_info(DB_ID(N'myDatabase'));

GO

SELECT TOP (20)

    file_id,

    vlf_begin_offset / 1024.0 / 1024.0 AS vlf_begin_mb,

    vlf_size_mb,

    vlf_active,

    vlf_status,

    vlf_sequence_number

FROM sys.dm_db_log_info(DB_ID(N'myDatabase'))

ORDER BY file_id DESC, vlf_begin_offset DESC;

GO

Bibliography