PROC: Convert LSN from hex to decimal for fn_dblog()

I was working out some code recently for a presentation on transaction log internals. Part of the presentation was showing the records in the transaction log, using the system function fn_dblog. I’m not going to go into a full description of fn_dblog right now, but I will get into some of it’s uses in a later post. It’s pretty fantastic. :-)

As part of the demonstration I was writing, I wanted to only show a few specific records in the transaction log. Sometimes, that’s easily done by using a WHERE clause with fn_dblog, and specifying a specific column, where that column is not null. For example, if you were looking for the number of page splits in the current transaction log, you could use:

select count(*)
from fn_dblog(null,null)
where [New Split Page] is not null;

Sometimes, though, you want to show a range of records and cannot find a good column to filter by. Or maybe you just want to start at a specific place in the log without regards to a particular column.  In that case, fn_dblog accepts a start LSN, and an end LSN as parameters. You just pass in those LSNs instead of the (null,null), and it will only show the log records that fall between those two LSN numbers. Simple, right?

Well, I wish it was that simple. You see, when you select from fn_dblog, the [Current LSN] column that is returned shows the LSNs in a three-part hexadecimal format; three hexadecimal numbers, separated by colons, like “000000a4:000000d8:0001”. However, if you want to pass those back to fn_dblog as parameters, you have to pass them as three=part decimal numbers, instead. So instead of…

select * from fn_dblog('000000a4:000000d8:0001',null);

…you would convert each hexadecimal part to decimal and…

select * from fn_dblog('00000164:00000216:0001',null);

And the first record that would be returned would have a [Current LSN] number of “000000a4:000000d8:0001”.

Nope, doesn’t make sense to me either.

However, rather than deal with the pain of doing the conversion math every single time I had to grab an LSN during my demo, I created a stored procedure to use for it. I placed it in the [master] database, and just call it every time I need to convert from one format to the other. The code is below.

Now, I’m in no way a developer, so I figure someone out there has to know a better way to do this. If you do, please leave a comment or drop me a line. I’d love to hear it.

Thanks,

-David.


USE [master];
GO

IF (SELECT OBJECT_ID('dbo.ConvertLSNHexToDec')) IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.ConvertLSNHexToDec
AS
PRINT ''STUB VERSION - Replace with actual procedure.'';
')
END
GO

ALTER PROCEDURE dbo.ConvertLSNHexToDec
/*******************************************************************************
AUTHOR:
David Maxwell

DATE:
April 2015

DESCRIPTION:
Converts the LSN in fn_dblog() from a three part hex to three part decimal,
so you can pass it back to fn_dblog() as a parameter. (Because inconsistency.)

DEPENDENCIES:
none

TEST EXEC:
EXEC dbo.ConvertLSNHexToDec @lsn = N''
GO

TO DO LIST:
1. Find a better way to do this other than this proc.
2. Add an option to do this either in three part format or an integer string.

NOTES:
Please send any comments or suggestions to dmmaxwell@gmail.com.

CHANGE HISTORY:
Date - Author - Change
April 2015 - David Maxwell - initial version - pending feedback

*******************************************************************************/
@lsn nvarchar(22) = NULL /* The LSN from fn_dblog that you want to convert */
AS

set nocount on;

declare @cmd nvarchar(2048), /* Dynamic SQL required for this, explained below */
@declsn nchar(22); /* The resulting, decimal-formatted LSN. */

/* In order to do this and keep everything in a three part format, I have to break
up the LSN into its three component parts via string parsing. So this makes the
hux numbers turn into strings. You can't convert a string to an int.

So we create a dynamic SQL query and execute it, bypassing any conversions
when we break this apart into strings.
*/

select @cmd = '
select
right(replicate(''0'',8) + cast(convert(int, 0x' + left(@lsn,8) + ') as nvarchar(8)),8) + N'':'' +
right(replicate(''0'',8) + cast(convert(int, 0x' + substring(@lsn,10,8) + ') as nvarchar(8)),8) + N'':'' +
right(replicate(''0'',4) + cast(convert(int, 0x' + right(@lsn,4) + ') as nvarchar(4)),4)
';

print @cmd; /* This is just here for troubleshooting, if this fails. */

/* Execute the dynamic SQL, and output to the @declsn variable. */
exec sp_executesql @stmt = @cmd, @declsn = @declsn output;

/* Here's your LSN. */
return @declsn
go