Convert CreateLSN of DBCC LOGINFO into time

If I run the undocumented DBCC LOGINFO against my database one of the fields returned is CreateLSN. This is the LSN in the format 1629000000070500011 at the time the VLF was created. Now it is a long time later and the auto grow values of Disk Usage report are long gone. I want to convert the LSN to a date/time

For a one off solution I tried this

select sys.fn_cdc_map_lsn_to_time ( convert(binary,1629000000070500011) )

But I get the error


Invalid object name 'cdc.lsn_time_mapping'

I feel like I am missing something simple, but it is alluding me.

sys.fn_cdc_map_lsn_to_time (Transact-SQL)

The LSN is incremented each time a modification is made to the database, irrespective of when the modification was made.

sys.fn_cdc_map_lsn_to_time relies on the current database having Change Data Capture enabled, which automatically creates several objects in the cdc schema. The definition of fn_cdc_map_lsn_to_time shows this:

create function [sys].[fn_cdc_map_lsn_to_time]              
(                                                       
    @lsn        binary(10)                                  
)                                                       
returns datetime
with returns null on null input
as                                                  
begin                                                   
    declare @lsn_end_time datetime

    select @lsn_end_time = tran_end_time                            
    from [cdc].[lsn_time_mapping]
    where start_lsn = @lsn

    return @lsn_end_time                                        
end

So, unless the current database has the cdc.lsn_time_mapping object (I'm unsure if this is a view or a table) present, the function will not work.

The notes in the docs page you linked says:


This function can be used to determine the time that a change was committed based upon the __$start_lsn value returned in the row of change data.

I'm extrapolating that the function actually returns pre-recorded LSN-to-timestamp tuples, instead of actually converting an LSN into a timestamp.

@RemusRusanu has an excellent writeup about LSNs on his blog

StackExchange Database Administrators Q#183108, answer score: 5