Michael J. Swart

August 9, 2022

Formatting Binary(10) LSN Values For Use In sys.fn_dblog()

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 3:06 pm

System procedures like sp_replincrementlsn and system functions like fn_cdc_get_min_lsn and fn_cdc_get_max_lsn return values that are of type binary(10).

These values represent LSNs, Log Sequence Numbers which are an internal way to represent the ordering of transaction logs.

Typically as developers, we don’t care about these values. But when we do want to dig into the transaction log, we can do so with sys.fn_dblog which takes two optional parameters. These parameters are LSN values which limit the results of sys.fn_dblog. But the weird thing is that sys.fn_dblogis a function whose LSN parameters are NVARCHAR(25).

The function sys.fn_dblog doesn’t expect binary(10) values for its LSN parameters, it wants the LSN values as a formatted string, something like: 0x00000029:00001a3c:0002.

Well, to convert the binary(10) LSN values into the format expected by sys.fn_dblog, I came up with this function:

CREATE OR ALTER FUNCTION dbo.fn_lsn_to_dblog_parameter(
    @lsn BINARY(10)
      STUFF (
        STUFF (
          '0x' + CONVERT(NVARCHAR(25), @lsn, 2),
          11, 0, ':' ),
        20, 0, ':' ),


I can increment the LSN once with a no-op and get back the lsn value with sp_replincrementlsn.
I can then use fn_lsn_to_dblog_parameter to get an LSN string to use as parameters to sys.fn_dblog.
This helps me find the exact log entry in the transaction that corresponds to that no-op:

DECLARE @lsn binary(10);
DECLARE @lsn_string nvarchar(25)
exec sp_replincrementlsn @lsn OUTPUT;
SET @lsn_string = dbo.fn_lsn_to_dblog_parameter(@lsn);
select @lsn_string as lsn_string, [Current LSN], Operation
from sys.fn_dblog(@lsn_string, @lsn_string);

1 Comment »

  1. […] Michael J. Swart does a bit of shuffling: […]

    Pingback by Formatting Binary LSN Values – Curated SQL — August 10, 2022 @ 8:10 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress