Querying the sys.dm_db_log_info() function while reducing to the max

Question

I'm currently querying the sys.dm_db_log_info() DMV to retrieve the VLFs from a database to determine when I can shrink, reorganise and reduce the amount of fragmented (10 MB VLFs) in the TLOG file.

The reason for this is that you can't shrink the TLOG file if a transaction is at the end of the TLOG file and results in an active VLF. Similar situation if an active transaction resides in the middle of the TLOG file, then you can't shrink past that VLF.

Current Statement

I currently have this statement to retrieve the MAX(vlf_begin_offset) record, the MIN(vlf_begin_offset) record and any record with an active vlf_active = 1:

SELECT ddli.vlf_begin_offset,
       ddli.vlf_sequence_number,
       ddli.vlf_active,
       ddli.vlf_status,
       ddli.vlf_first_lsn
FROM   sys.dm_db_log_info(DB_ID()) AS ddli
WHERE  ddli.vlf_begin_offset = (
           SELECT MIN(ddli2.vlf_begin_offset)
           FROM   sys.dm_db_log_info(DB_ID()) AS ddli2
       )
       OR  ddli.vlf_active = 1
       OR  ddli.vlf_begin_offset = (
               SELECT MAX(ddli3.vlf_begin_offset)
               FROM   sys.dm_db_log_info(DB_ID()) AS ddli3
           )
ORDER BY
       ddli.vlf_begin_offset ASC

When all the records are returned the result set looks like this:

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|           262144 |              253979 |          0 |          0 | 00000000:00000000:0000 |
|           516096 |              253980 |          0 |          0 | 00000000:00000000:0000 |
|           770048 |              253977 |          0 |          0 | 00000000:00000000:0000 |
|          1048576 |              253981 |          0 |          0 | 00000000:00000000:0000 |
|         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |
|         67108864 |              253971 |          0 |          0 | 00000000:00000000:0000 |
|         75497472 |              253972 |          0 |          0 | 00000000:00000000:0000 |
|         83886080 |              253973 |          0 |          0 | 00000000:00000000:0000 |
|         92274688 |              253974 |          0 |          0 | 00000000:00000000:0000 |
|        100663296 |              253975 |          0 |          0 | 00000000:00000000:0000 |
|        109051904 |              253976 |          0 |          0 | 00000000:00000000:0000 |
|        117440512 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        236978176 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        356515840 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        476053504 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        595591168 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        715128832 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        834666496 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

With my current script I get:

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

Additional Information

The vlf_active = 1 can pop up in various locations. There can be more than one island with vlf_active = 1.

Questions

It would be nice to have a record before any active vlf (vlf_active = 1) and one record after.

How can I achieve this?

  +------------------+---------------------+------------+------------+------------------------+
  | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
  +------------------+---------------------+------------+------------+------------------------+
  |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
  |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
  |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
  |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
  |        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
  +------------------+---------------------+------------+------------+------------------------+

What would be required to have a summary record in between if there are hundreds of records? e.g. something like this:

  +------------------+---------------------+------------+------------+------------------------+
  | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
  +------------------+---------------------+------------+------------+------------------------+
  |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
  |  (4 res clipped) |                     |            |            |                        |
  |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
  |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
  |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
  | (13 res clipped) |                     |            |            | 00000000:00000000:0000 |    
  |        954204160 |                   0 |          0 |          0 |                        |
  +------------------+---------------------+------------+------------+------------------------+

Paul White · Accepted Answer · 2022-11-02 13:23:26Z

Requirement

Using your sample data:

DECLARE @LogInfo table
(
    vlf_begin_offset bigint NULL UNIQUE CLUSTERED,
    vlf_sequence_number bigint NULL,
    vlf_active bit NOT NULL,
    vlf_status integer NULL,
    vlf_first_lsn nvarchar(24) NULL
);

INSERT @LogInfo
VALUES
    (     8192, 253978, 0, 0, N'00000000:00000000:0000'),
    (   262144, 253979, 0, 0, N'00000000:00000000:0000'),
    (   516096, 253980, 0, 0, N'00000000:00000000:0000'),
    (   770048, 253977, 0, 0, N'00000000:00000000:0000'),
    (  1048576, 253981, 0, 0, N'00000000:00000000:0000'),
    ( 17563648, 253982, 0, 0, N'00000000:00000000:0000'),
    ( 34078720, 253983, 1, 2, N'0003e01f:00000010:0001'),
    ( 50593792, 253970, 0, 0, N'00000000:00000000:0000'),
    ( 67108864, 253971, 0, 0, N'00000000:00000000:0000'),
    ( 75497472, 253972, 0, 0, N'00000000:00000000:0000'),
    ( 83886080, 253973, 0, 0, N'00000000:00000000:0000'),
    ( 92274688, 253974, 0, 0, N'00000000:00000000:0000'),
    (100663296, 253975, 0, 0, N'00000000:00000000:0000'),
    (109051904, 253976, 0, 0, N'00000000:00000000:0000'),
    (117440512,      0, 0, 0, N'00000000:00000000:0000'),
    (236978176,      0, 0, 0, N'00000000:00000000:0000'),
    (356515840,      0, 0, 0, N'00000000:00000000:0000'),
    (476053504,      0, 0, 0, N'00000000:00000000:0000'),
    (595591168,      0, 0, 0, N'00000000:00000000:0000'),
    (715128832,      0, 0, 0, N'00000000:00000000:0000'),
    (834666496,      0, 0, 0, N'00000000:00000000:0000'),
    (954204160,      0, 0, 0, N'00000000:00000000:0000');

You want to see:

The first row in offset order
The last row in offset order
Any row with active = 1
Any row where the next row in offset order has active = 1
Any row where the prior row in offset order had active = 1

Solution

We can do all that with an offset-ordered window frame covering a maximum of three rows: the prior row, the current row, and the following row.

The first and last rows have only two rows in their frames. The first row lacks a prior row and the last row lacks a following row. We are always interested in these rows, regardless of the active value. Other interesting rows will have active = 1 in one of their frame's rows.

Note: If there is only one VLF, the frame will have only one row, but that row must be active. The active row will always be 'interesting'.

Another way to express this to say we are interested in both:

Rows with only two rows in in their three-row frame
Rows with active = 1 in any frame row

We can encapsulate that in the following logic:

SELECT
    Q1.vlf_begin_offset,
    Q1.vlf_sequence_number,
    Q1.vlf_active,
    Q1.vlf_status,
    Q1.vlf_first_lsn
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                -- First & last rows have only two in frame
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                -- Any row in frame is active
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
WHERE
    Q1.is_interesting = 1
ORDER BY
    Q1.vlf_begin_offset;

Results:

vlf_begin_offset	vlf_sequence_number	vlf_active	vlf_status	vlf_first_lsn
8192	253978	0	0	00000000:00000000:0000
17563648	253982	0	0	00000000:00000000:0000
34078720	253983	1	2	0003e01f:00000010:0001
50593792	253970	0	0	00000000:00000000:0000
954204160	0	0	0	00000000:00000000:0000

With summary records

It's only a little more difficult to produce the summary of hidden records shown in the question. We first need a way to identify contiguous groups of rows we want to skip.

This can be done by summing the number of interesting rows in the offset-ordered frame prior to and including the current row:

SELECT
    Q1.*,
    grp = 
        SUM(Q1.is_interesting) OVER (
            ORDER BY Q1.vlf_begin_offset 
            ROWS UNBOUNDED PRECEDING)
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
ORDER BY
    Q1.vlf_begin_offset;

Results:

vlf_begin_offset	vlf_sequence_number	vlf_active	vlf_status	vlf_first_lsn	is_interesting	grp
8192	253978	0	0	00000000:00000000:0000	1	1
262144	253979	0	0	00000000:00000000:0000	0	1
516096	253980	0	0	00000000:00000000:0000	0	1
770048	253977	0	0	00000000:00000000:0000	0	1
1048576	253981	0	0	00000000:00000000:0000	0	1
17563648	253982	0	0	00000000:00000000:0000	1	2
34078720	253983	1	2	0003e01f:00000010:0001	1	3
50593792	253970	0	0	00000000:00000000:0000	1	4
67108864	253971	0	0	00000000:00000000:0000	0	4
75497472	253972	0	0	00000000:00000000:0000	0	4
83886080	253973	0	0	00000000:00000000:0000	0	4
92274688	253974	0	0	00000000:00000000:0000	0	4
100663296	253975	0	0	00000000:00000000:0000	0	4
109051904	253976	0	0	00000000:00000000:0000	0	4
117440512	0	0	0	00000000:00000000:0000	0	4
236978176	0	0	0	00000000:00000000:0000	0	4
356515840	0	0	0	00000000:00000000:0000	0	4
476053504	0	0	0	00000000:00000000:0000	0	4
595591168	0	0	0	00000000:00000000:0000	0	4
715128832	0	0	0	00000000:00000000:0000	0	4
834666496	0	0	0	00000000:00000000:0000	0	4
954204160	0	0	0	00000000:00000000:0000	1	5

The desired output shows no data for uninteresting rows, so replace them with nulls:

SELECT
    vlf_begin_offset = IIF(Q1.is_interesting = 1, Q1.vlf_begin_offset, NULL),
    vlf_sequence_number = IIF(Q1.is_interesting = 1, Q1.vlf_sequence_number, NULL),
    vlf_active = IIF(Q1.is_interesting = 1, Q1.vlf_active, NULL),
    vlf_status = IIF(Q1.is_interesting = 1, Q1.vlf_status, NULL),
    vlf_first_lsn = IIF(Q1.is_interesting = 1, Q1.vlf_first_lsn, NULL),
    Q1.is_interesting,
    grp = 
        SUM(Q1.is_interesting) OVER (
            ORDER BY Q1.vlf_begin_offset 
            ROWS UNBOUNDED PRECEDING)
FROM 
(
    SELECT 
        LI.*,
        is_interesting =
            IIF
            (
                2 =
                COUNT_BIG(*) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                OR 1 =
                MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                    ORDER BY LI.vlf_begin_offset 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                1,
                0
            )
    FROM @LogInfo AS LI
) AS Q1
ORDER BY
    Q1.vlf_begin_offset;

Results:

vlf_begin_offset	vlf_sequence_number	vlf_active	vlf_status	vlf_first_lsn	is_interesting	grp
8192	253978	0	0	00000000:00000000:0000	1	1
NULL	NULL	NULL	NULL	NULL	0	1
NULL	NULL	NULL	NULL	NULL	0	1
NULL	NULL	NULL	NULL	NULL	0	1
NULL	NULL	NULL	NULL	NULL	0	1
17563648	253982	0	0	00000000:00000000:0000	1	2
34078720	253983	1	2	0003e01f:00000010:0001	1	3
50593792	253970	0	0	00000000:00000000:0000	1	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
NULL	NULL	NULL	NULL	NULL	0	4
954204160	0	0	0	00000000:00000000:0000	1	5

All the uninteresting rows we need to summarize are now identical within their groups. We can remove the duplicates and count the rows using a plain GROUP BY.

Final query

The finishing touches produce the customized offset column text and ensure the rows appear in the correct order:

SELECT
    vlf_begin_offset =
        IIF
        (
            Q2.is_interesting = 1,
            CONVERT(sql_variant, Q2.vlf_begin_offset),
            CONCAT('(', COUNT_BIG(*), ' res clipped)')
        ),
    Q2.vlf_sequence_number,
    Q2.vlf_active,
    Q2.vlf_status,
    Q2.vlf_first_lsn
FROM 
(
    SELECT 
        vlf_begin_offset = IIF(Q1.is_interesting = 1, Q1.vlf_begin_offset, NULL),
        vlf_sequence_number = IIF(Q1.is_interesting = 1, Q1.vlf_sequence_number, NULL),
        vlf_active = IIF(Q1.is_interesting = 1, Q1.vlf_active, NULL),
        vlf_status = IIF(Q1.is_interesting = 1, Q1.vlf_status, NULL),
        vlf_first_lsn = IIF(Q1.is_interesting = 1, Q1.vlf_first_lsn, NULL),
        Q1.is_interesting,
        grp = 
            SUM(Q1.is_interesting) OVER (
                ORDER BY Q1.vlf_begin_offset 
                ROWS UNBOUNDED PRECEDING)
    FROM 
    (
        SELECT 
            LI.*,
            is_interesting =
                IIF
                (
                    2 =
                    COUNT_BIG(*) OVER (
                        ORDER BY LI.vlf_begin_offset 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                    OR 1 =
                    MAX(CONVERT(bigint, LI.vlf_active)) OVER (
                        ORDER BY LI.vlf_begin_offset 
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
                    1,
                    0
                )
        FROM @LogInfo AS LI
    ) AS Q1
) AS Q2
GROUP BY 
    Q2.vlf_begin_offset,
    Q2.vlf_sequence_number,
    Q2.vlf_active,
    Q2.vlf_status,
    Q2.vlf_first_lsn,
    Q2.is_interesting,
    Q2.grp
ORDER BY
    Q2.grp,
    Q2.is_interesting DESC;

Results:

vlf_begin_offset	vlf_sequence_number	vlf_active	vlf_status	vlf_first_lsn
8192	253978	0	0	00000000:00000000:0000
(4 res clipped)	NULL	NULL	NULL	NULL
17563648	253982	0	0	00000000:00000000:0000
34078720	253983	1	2	0003e01f:00000010:0001
50593792	253970	0	0	00000000:00000000:0000
(13 res clipped)	NULL	NULL	NULL	NULL
954204160	0	0	0	00000000:00000000:0000

Replace the single reference to the table variable with the DMV call to try it out on your server.

Paul White · Accepted Answer · 2022-10-30 15:35:00Z

Window functions are what you need here. LAG and LEAD will tell what the previous and next rows' values are, and will return NULL if there is no such row (in other words the first or last row).

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      prev_active = LAG (vlf_active) OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active) OVER (ORDER BY vlf_begin_offset)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE (
      ddli.vlf_active = 1
   OR ddli.prev_active IS NULL
   OR ddli.prev_active = 1
   OR ddli.next_active IS NULL
   OR ddli.next_active = 1
)
ORDER BY
   ddli.vlf_begin_offset;

You can simplify the logic if you use the default parameter of LEAD and LAG

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      prev_active = LAG (vlf_active, 1, 1) OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active, 1, 1) OVER (ORDER BY vlf_begin_offset)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE 1 IN (ddli.vlf_active, ddli.prev_active, ddli.next_active)
ORDER BY
   ddli.vlf_begin_offset;

Another option is to use a conditional COUNT or a SUM, windowed over the row before and the row after. You still need LEAD LAG or ROW_NUMBER to identify the first and last rows, so it doesn't gain you much.

SELECT
  ddli.vlf_begin_offset,
  ddli.vlf_sequence_number,
  ddli.vlf_active,
  ddli.vlf_status,
  ddli.vlf_first_lsn
FROM  (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY vlf_begin_offset),
      next_active = LEAD(vlf_active) OVER (ORDER BY vlf_begin_offset),
      any_active = COUNT(NULLIF(vlf_active, 0)) OVER (ORDER BY vlf_begin_offset ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
    FROM sys.dm_db_log_info(DB_ID()) ddli
) ddli
WHERE ddli.rn = 1
   OR ddli.any_active > 0
   OR ddli.next_active IS NULL
ORDER BY
   ddli.vlf_begin_offset;

Stack Exchange Network

Querying the sys.dm_db_log_info() function while reducing to the max

Current Statement

Questions

2 Answers 2

Requirement

Solution

With summary records

Final query

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
query
dmv
or ask your own question.

Linked

Hot Network Questions

Querying the sys.dm_db_log_info() function while reducing to the max

Current Statement

Questions

2 Answers 2

Requirement

Solution

With summary records

Final query

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2016querydmv or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2016
query
dmv
or ask your own question.