Michael J. Swart

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)
)
RETURNS NVARCHAR(25)
AS 
BEGIN
  RETURN
    NULLIF(
      STUFF (
        STUFF (
          '0x' + CONVERT(NVARCHAR(25), @lsn, 2),
          11, 0, ':' ),
        20, 0, ':' ),
      '0x00000000:00000000:0000'
    )
END
GO

Example

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);