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