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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
RAISERROR('Create function: [dbo].[ufn_convertLSNToNumeric]', 10, 1) WITH NOWAIT GO IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ufn_convertLSNToNumeric]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[ufn_convertLSNToNumeric] GO CREATE FUNCTION [dbo].[ufn_convertLSNToNumeric] ( @LSN [varchar](22) ) RETURNS [numeric](25) /* WITH ENCRYPTION */ AS -- ============================================================================ -- Author : Dan Andrei STEFAN -- Create date : 26.05.2015 -- Module : Database Analysis & Performance Monitoring -- ============================================================================ /* http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/ 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 00000001:00000001:0001 */ begin RETURN CAST(CAST(CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + LEFT(@LSN, 8), 8), 1) As int) AS VARCHAR(32)) as varchar(8)) + CAST(RIGHT(REPLICATE('0', 10) + CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + SUBSTRING(@LSN, 10, 8), 8), 1) As int) AS VARCHAR(32)), 10) as varchar(10)) + CAST(RIGHT(REPLICATE('0', 5) + CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + RIGHT(@LSN, 4), 8), 1) As int) AS VARCHAR(32)), 5) as varchar(5)) AS [numeric](25)) end GO |
4 Responses to Convert LSN to Numeric
Daniel Adeniji December 11, 2015
Dan:
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
Dan Andrei STEFAN December 11, 2015
Being a scalar function, can be used along with fn_dblog or fn_dump_dblog.