Convert an LSN from Hex to Decimal
Log Sequence Numbers (LSNs) can be displayed and consumed in several formats. This post shows how to quickly and easily convert between the “typical” hexidecimal format, and the equivalent decimal format. Hexidecimal format LSNs appear in result sets returned by fn_dblog, and look like 00000268:0000356c:0001. However, the sys.fn_dblog demands input LSNs be in decimal format. The decimal format looks like 616:13676:1 for that same LSN.
Jean-Frederic Schall’s paiting of this lady is far nicer than my LSN conversion tool.
Conversion consists of getting each chunk from the hexidecimal form, in varchar format, followed by converting that number to an integer, then re-assembling the three parts.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @lsnc varchar(25) = '00000268:0000356c:0001'; DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@lsnc, 8), 0), 1) , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@lsnc, 10, 8), 0), 1) , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@lsnc, 4), 0), 1); SELECT @lsnc = CONVERT(varchar(8), @a, 1) + ':' + CONVERT(varchar(8), @b, 1) + ':' + CONVERT(varchar(8), @c, 1) SELECT @lsnc; |
The output looks like:
The code above can be easily converted to a schema-bound table-valued-function like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DROP FUNCTION IF EXISTS dbo.ConvertHexLSN GO CREATE FUNCTION dbo.ConvertHexLSN ( @lsnc varchar(25) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT TOP(1) LSNd = CONVERT(varchar(8), CONVERT(int, CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@lsnc, 8), 0), 1), 1)) + ':' + CONVERT(varchar(8), CONVERT(int, CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@lsnc, 10, 8), 0), 1)), 1) + ':' + CONVERT(varchar(8), CONVERT(int, CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@lsnc, 4), 0), 1), 1)) ); GO |
Let me know if you have any questions about this post.
Check out the rest of our series on internals.