CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alexandre
Alexandre

Reputation: 2025

What format should the start and end LSN parameters be to sys.fn_dblog?

When I execute

SELECT [Current LSN]
FROM sys.fn_dblog(NULL,NULL)

The LSN values are presented in the format

'000000CB:00000055:0002'

However when I try and pass the start and end LSN parameters explicitly to fn_dblog in that format

SELECT *
FROM   sys.fn_dblog('000000CB:00000055:0002', '000000CC:00000088:000A')

I get an error, saying...

Msg 9005, Level 16, State 3, Line 1 Either start LSN or end LSN specified in OpenRowset(DBLog, ...) is invalid.

Why doesn't this work and what format do they need to be in?

I did some studies based on this site about LSN and log files... but, or I got an error, or, when I execute the command with the two parameters, I just got 2 rows of informations... I considerate that is intresting, because, when I use a program to read the data-log, I got all the information's.

How I can make to read all the data and informations of log using SQL ?

enter image description here

enter image description here

Upvotes: 3

Views: 4870

Answers (2)

Simon Francesco
Simon Francesco

Reputation: 1561

FYI: As of SQL Server 12 (I haven't tested others but as far back as SQL 2000 by the looks), you can add '0x' to the beginning of the LSN text.

SELECT TOP 1 [Current LSN] FROM fn_dblog('0x000000CB:00000055:0002',null)

Obviously if the query returns with no errors and the same LSN in the result then you should be good to go.

Credit to Art of SQL

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453358

There are a couple of issues in your question.

Firstly regarding your question about LSN formats you need to convert the hexadecimal numbers to decimal to get the expected parameter format.

Example code to convert 000000CC:00000088:000A is

DECLARE @LSN_HEX_SEP NVARCHAR(23) = '000000CC:00000088:000A'

DECLARE @N1 BIGINT = CONVERT(varbinary,SUBSTRING(@LSN_HEX_SEP, 1, 8),2),
        @N2 BIGINT = CONVERT(varbinary,SUBSTRING(@LSN_HEX_SEP, 10, 8),2),
        @N3 BIGINT = CONVERT(varbinary,SUBSTRING(@LSN_HEX_SEP, 19, 4),2)

SELECT CAST(@N1 AS VARCHAR) + ':' +
      CAST(@N2 AS VARCHAR) + ':' +
      CAST(@N3 AS VARCHAR)

So the call With both converted would look like

Select * 
from sys.fn_dblog('203:85:2', '204:136:10')

But This isn't going to help you

The screenshot you show after running

SELECT *
FROM sys.fn_dblog(NULL,NULL)

Shows your active log only contains two rows and they are not in the LSN range you are trying to find anyway.

Passing parameters to the function just filters these rows. It will not show you any additional ones (and you will get a log scan error looking for non existent ones).

The relevant log records may well already have been lost. Trace Flag 2357 can be used to show records from the inactive log though.

You can use

DBCC TRACEON(2537)

SELECT *
FROM sys.fn_dblog(NULL,NULL)

To see those records.

I suggest saving these in a table in a different database ASAP. You can then peruse them at leisure to see if any useful log records were saved.

Though; how easy decoding them would be, very much depends anyway.

Upvotes: 4

Related Questions