Consultando a função sys.dm_db_log_info() enquanto reduz ao máximo

Funções de janela são o que você precisa aqui. LAGe LEADdirá quais são os valores das linhas anteriores e seguintes, e retornará NULLse não houver tal linha (em outras palavras, a primeira ou a última linha).

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;

Você pode simplificar a lógica se usar o defaultparâmetro de LEADeLAG

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;

Outra opção é usar um condicional COUNTou um SUM, janelado sobre a linha anterior e a linha posterior. Você ainda precisa identificar a primeira LEAD LAGe ROW_NUMBERa última linhas, então não ganha muito.

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;

Requerimento

Usando seus dados de amostra:

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');

Você quer ver:

• A primeira linha em ordem de deslocamento
• A última linha em ordem de deslocamento
• Qualquer linha com ativo = 1
• Qualquer linha em que a próxima linha na ordem de deslocamento esteja ativa = 1
• Qualquer linha em que a linha anterior na ordem de deslocamento estava ativa = 1

Solução

Podemos fazer tudo isso com um quadro de janela ordenado por deslocamento cobrindo no máximo três linhas: a linha anterior, a linha atual e a linha seguinte.

A primeira e a última linha têm apenas duas linhas em seus quadros. A primeira linha não possui uma linha anterior e a última linha não possui uma linha seguinte. Estamos sempre interessados ​​nessas linhas, independentemente do valor ativo. Outras linhas interessantes terão active = 1 em uma das linhas de seus quadros.

Nota: Se houver apenas um VLF, o quadro terá apenas uma linha, mas essa linha deverá estar ativa. A linha ativa sempre será 'interessante'.

Outra maneira de expressar isso para dizer que estamos interessados ​​em ambos:

• Linhas com apenas duas linhas em seu quadro de três linhas
• Linhas com ativo = 1 em qualquer linha de quadro

Podemos encapsular isso na seguinte lógica:

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;

Resultados:

Com registros de resumo

É apenas um pouco mais difícil produzir o resumo dos registros ocultos mostrados na pergunta. Primeiro, precisamos de uma maneira de identificar grupos contíguos de linhas que queremos pular.

Isso pode ser feito somando o número de linhas interessantes no quadro ordenado por deslocamento antes e incluindo a linha atual:

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;

Resultados:

A saída desejada não mostra dados para linhas desinteressantes, portanto, substitua-as por nulos:

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;

Resultados:

Todas as linhas desinteressantes que precisamos resumir agora são idênticas dentro de seus grupos. Podemos remover as duplicatas e contar as linhas usando um arquivo GROUP BY.

Consulta final

Os toques finais produzem o texto da coluna de deslocamento personalizado e garantem que as linhas apareçam na ordem correta:

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;

Resultados:

Substitua a única referência à variável de tabela pela chamada DMV para experimentá-la em seu servidor.