« first day (2191 days earlier)      last day (844 days later) » 

John K. N.
John K. N.
12:22
I'm currently querying the sys.dm_db_log_info() information and would like to squash the output from 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
To somehting like this:
Erik Darling
Erik Darling
i would also like to squash it to something like that
John K. N.
John K. N. 
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
Paul White
Paul White
I think you can do that quite easily by...
John K. N.
John K. N.
So basicall the MIN() record, the MAX() record the records containing vlf_active state of 1 and then one before that and one after.
Is that a GAP and ISLANDS problem?
Paul White
Paul White
...asking a question on the main site
You'll find formatting works better there too
John K. N.
John K. N.
12:25
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
Paul White
Paul White
OMG stahp
John K. N.
John K. N.
:-(
Paul White
Paul White
Just ask a question on main. It's already several screenfuls here
Charlieface
Charlieface
Where does 954204160
come from?
John K. N.
John K. N.
I don't want to come across as Do this for me.
@Charlieface vlf_begin_offset
Is it a gap and islands problem?
Charlieface
Charlieface
12:28
@JohnK.N. Ah sorry didn't realize the table was longer
wot he said: the formatting is much better on the main site
Erik Darling
Erik Darling
imagine how useful these clarifications would be in a question on main
Paul White
Paul White
ikr
John K. N.
John K. N.
→ 8 messages moved to Trash
← 8 messages moved from The Heap™ – Consultancy ©®
Erik Darling
Erik Darling
i think it'd be stunning
Paul White
Paul White
I'd be stunned for sure
Charlieface
Charlieface
12:30
Looks like ROW_NUMBER = 1 OR LEAD(vlf_active, 1, 1) = 1 OR LAG(vlf_active) = 1 obviously in a CTE/derived table
Paul White
Paul White
← 19 messages moved from The Heap™ – Consultancy ©®

« first day (2191 days earlier)      last day (844 days later) »