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

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 |

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;


StackExchange Database Administrators Q#318895, answer score: 5