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

Question

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

What are you trying to achieve by attempting to read the transaction log file directly?? It's really not intended for that use ... — marc_s, Dec 27 '13 at 13:16
OK, and again: what are you **trying to achieve** by reading the table updates? — marc_s, Dec 27 '13 at 13:18
I just want to get the informations of updates... and what were the changes in the table, what values have changed; if the program can do this, perhaps making a direct parse the log file, do not know, I believe you have another way to do this.... — Alexandre, Dec 27 '13 at 13:20
I don't know what you're expecting to find in the transaction log - but you **will NOT** find SQL statements like `UPDATE ... SET .... WHERE....` in there - you'll find **low-level, SQL Server internals-specific** instructions like *read page no. 123, insert these bytes into slot #5* and so forth.... — marc_s, Dec 27 '13 at 13:21
[This link to a blog post](http://solutioncenter.apexsql.com/read-a-sql-server-transaction-log/) can show you what you might find in the SQL Server transaction log - if you're able to read it — marc_s, Dec 27 '13 at 13:47
possible duplicate of [How to view transaction logs in sql server 2008](http://stackoverflow.com/questions/4507509/how-to-view-transaction-logs-in-sql-server-2008) — marc_s, Dec 27 '13 at 13:49
@marc_s - I expect they are trying to achieve [recovering from this error](http://stackoverflow.com/questions/20786494/how-to-restore-a-database-from-the-sql-2008r2-log) without forking over $1,500 to Apex. — Martin Smith, Dec 27 '13 at 20:41

score 5 · Answer 1 · answered Mar 03 '15 at 03:25

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

Martin Smith · Accepted Answer · 2013-12-28T10:29:43.593

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.

Why in your example you use `DECLARE @LSN_HEX_SEP NVARCHAR(23) = '000000CC:00000088:000A'` and 4 symbols as last part? In my server I see values like `000a4444:fdae4f71:13361` — Hubbitus, Dec 25 '19 at 18:55

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

2 Answers2

Linked