31 Days of Disaster Recovery

Summary

Hopefully I was successful in stressing the importance of raising alerts and sending notifications for these errors. It is critical that we identify and deal with corruption as soon as possible to reduce the chance that we will lose data. So create these alerts and learn how to handle them.

Day 11: Converting LSN Formats

Welcome back to my series 31 Days of Disaster Recovery. Today is day 11, and today I want to talk about converting LSN formats. I had intended to write this blog post a long time ago, but I never seemed to get around to it. This started out as a question posted on Twitter’s #sqlhelp hash tag.

Someone had read a blog post by Paul Randal (blog|@PaulRandal) called Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN. In this blog post, Paul explains how to take the LSN for a transaction from a log dump and use it in the RESTORE command with the STOPBEFOREMARK option. In this case, the LSN is in a different format than is required for the RESTORE command. Paul Randal explains how to convert the format, but the explanation was still a little confusing for some people. This post is an attempt to clarify how to convert the LSN format and also provide an automated way to convert it.

Converting LSN Format

If you get the LSN from a log dump with either fn_dblog() or DBCC LOG() or fn_dumpdblog(), the LSN format is in a string format that consists of 3 hexadecimal numbers delimited with colons. The restore command expects the LSN to be in a large integer format (we will actually be converting it to a varchar(26) data type). The process is to take each hexadecimal string and convert it and them put them back together again with the correct number of leading zeros.

We start with this format from the log file: :: (e.g., 00000467:00001fd8:0001) and convert it to this format . Easy peezy, lemon squeezy.

Paul Randal explained the format conversion really well in his post, but the part that was confusing some people was how to get the hexadecimal string to an integer string. With SQL Server 2008+, this is very simple to do by converting to a varbinary data type with a conversion style of 1 and then cast as integer. In order to convert it to varbinary, you first have to concatenate the string 0x and enough zeros to the hex string to bring the length of the hex string to 8 characters. Or to put into T-SQL terms:

CAST(CONVERT(VARBINARY, '0x' +

RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) As int);

We do this with each of the three hex strings and then concatenate them with the appropriate leading zeros. for example, the resulting integer string would be 1127000000815200001 [1127 + (000000 + 8152) + (0000 + 1)] If we convert each hex string to variables of @LSN1, @LSN2, and @LSN3, the final concatenation would be:

CAST(@LSN1 as varchar(8)) +

CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) as varchar(10)) +

CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) as varchar(5));

Testing the Process

First, we will create a database to perform our test in, make sure it is in simple recovery model, and then create a marked transaction:

Create Database TestLSN;

Go
-- Make sure it is simple recovery

Alter Database TestLSN Set Recovery Simple;

Go
Use TestLSN;

Go
-- Create a marked transaction

BEGIN TRAN Tran1 With MARK 'Tran 1';
Select * Into dbo.MyDatabases

From sys.databases;

Commit

Go

We can look up the LSN for this transaction (ignoring the logmarkhistory table in the msdb database) by dumping the log file and looking for the transaction by name.

-- Find the LSN in the log

Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name]

From fn_dblog(null, null)

Where [Transaction Name] = 'Tran1';

Current LSN	Previous LSN	Operation	Context	Transaction Name
0000001e:00000140:0004	00000000:00000000:0000	LOP_BEGIN_XACT	LCX_NULL	Tran1

But let’s assume that you don’t find about this right away and the transaction is no longer in the active part of the log. Does that mean that we’re out of luck? Here we will take a manual checkpoint and see that the transaction is no longer listed in the log:

-- Manual Checkpoint

Checkpoint;
-- Find the LSN in the log

Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name]

From fn_dblog(null, null)

Where [Transaction Name] = 'Tran1';

Maybe we’re not out of luck though. There is a chance it could still be in the inactive portion of the log. We can use undocumented trace flag 2537 to view the inactive portion of the log. There is a bug with this trace flag (which I’ve been told will never be fixed because, after all, it’s undocumented) which prevents it from being used to read the log from a newly restored database.

-- No, really. Find the LSN in the log

DBCC TraceOn (2537);
Select [Current LSN], [Previous LSN], Operation, Context, [Transaction Name]

From fn_dblog(null, null)

Where [Transaction Name] = 'Tran1';
DBCC TraceOff (2537);

So we have our LSN (Current LSN is the column we want) and can convert it now. Just plug the “Current LSN” into the conversion script to get the properly formatted LSN.

-- Convert LSN from hexadecimal string to decimal string

Declare @LSN varchar(22),

@LSN1 varchar(11),

@LSN2 varchar(10),

@LSN3 varchar(5),

@NewLSN varchar(26)

-- LSN to be converted to decimal

Set @LSN = '0000001e:00000038:0001';

-- Split LSN into segments at colon

Set @LSN1 = LEFT(@LSN, 8);

Set @LSN2 = SUBSTRING(@LSN, 10, 8);

Set @LSN3 = RIGHT(@LSN, 4);

-- Convert to binary style 1 -> int

Set @LSN1 = CAST(CONVERT(VARBINARY, '0x' +

RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) As int);
Set @LSN2 = CAST(CONVERT(VARBINARY, '0x' +

RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) As int);

Set @LSN3 = CAST(CONVERT(VARBINARY, '0x' +

RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) As int);

-- Add padded 0's to 2nd and 3rd string

Select CAST(@LSN1 as varchar(8)) +

CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) as varchar(10)) +

CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) as varchar(5));

This gives us the LSN in integer string format: 30000000005600001