0

I am trying to get the timestamp of the first entry in the .LDF file of a live database. Is there any way to do this?

I already get the first LSN in the LDF using

SELECT MIN([Current LSN]) 
FROM sys.fn_dblog(NULL,NULL)

This returns a string like 00000064:000000e9:0033. But how can I convert this into a timestamp? The function sys.fn_cdc_map_lsn_to_time throws an error that "cdc.lsn_time_mapping" is not defined. This is not surprising IMO, since CDC is not enabled on the server.

BTW: This is NOT a duplicate of my own question 83446 from last november: I don't have a TRN to parse, but only the .LDF.

4
  • sys.fn_dblog has [Begin Time]. Is that what you are looking for ?
    – Kin Shah
    Mar 4, 2015 at 17:27
  • See my answer below for more information, but what are you trying to accomplish with this data? What's the bigger picture? Mar 4, 2015 at 17:30
  • 1
    Read this - How to read and interpret the SQL Server log until you fully understand. I agree with @ThomasStringer that you are barking at the wrong tree :-)
    – Kin Shah
    Mar 4, 2015 at 17:35
  • @Kin: The link helped. I did see [Begin Time] before, but did not check it any further, since the column is not filled for all rows. But knowing the details about it now, it is close enough for me. Thank you!
    – Andreas
    Mar 5, 2015 at 8:23

1 Answer 1

1

This returns a String like '00000064:000000e9:0033'. But how can I convert this into a timestamp?

You can't. An LSN is not a timestamp, nor does it store any portion of date and/or time in it's bits.

SQL Server does not log the datetime stamp for every logged operation. It does keep track of these timestamps for transactions, but not for each and every single logged operation.

Note: You are trying to reference a Change Data Capture DMF with sys.fn_cdc_map_lsn_to_time.

I think you are taking the wrong approach to some sort of question that you are trying to answer. Datetime stamps on transaction log records wouldn't really tell you much, even if it is possible.

Let's take a step back and let us know why you're trying to do this.

2
  • About the impossibility: At least when I am restoring a database using trn-files, I can determine a point in time, where it should stop. That's why I thought, that there should be a possibility to convert LSNs to timestamps.
    – Andreas
    Mar 5, 2015 at 7:38
  • What I want to find out: The last time, the LDF was cleared.
    – Andreas
    Mar 5, 2015 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.