Jun 032015 Tagged with , 4 Responses

Convert LSN to Numeric

As part of the Database Corruption Challenge I needed to convert the LSN values returned by fn_dblog() / fn_dump_dblog() to numeric.

The math is not very simple and it is best described by Paul Randal.

  • Take the rightmost 4 characters (2-byte log record number) and convert to a 5-character decimal number, including leading zeroes, to get stringA
  • Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading zeroes, to get stringB
  • Take the leftmost number (4-byte VLF sequence number) and convert to a decimal number, with no leading zeroes, to get stringC
  • The LSN string we need is stringC + stringB + stringA

You may download the script that will create the function from here, or use the code below to build your own:


4 Responses to Convert LSN to Numeric
  1. […] to do the LSN conversion, and included it in the extra clue this week. Another participant Dan Andr... stevestedman.com/2015/06/week-7-database-corruption-challenge-results
  2. Daniel Adeniji Reply


    Can you please tell me what use is it to convert varchar LSN to its numeric representation.

    Do you pass it on to other SQL Commands?

    Nice work.

    Daniel Adeniji

  3. Dan Andrei STEFAN Reply

    Being a scalar function, can be used along with fn_dblog or fn_dump_dblog.

Leave a Reply

Your email address will not be published. Please enter your name, email and a comment.