- November 14, 2018 at 3:56 pm #16235ggoodrichKeymaster
How To: identify the SQL Server TLog backup log file based on the LSN
This procedure is helpful when you are unsure of the specific SQL Server transaction log backup log file needs to be produced for investigation.
For example below is the error message
2018-11-12T05:35:13-07:00: hub-cap-chn: F_JD0A6A: A trailing transaction log sector was encountered in the middle of the block at [000017DD:000E5565], sector=63. F_JT1410: The previous error occurred after scanning record 675817, which has LSN 000017dd:000e5505:0003 and timestamp 2018-11-10T22:08:20-08:00.
Overview of steps
- Convert the LSN three-part number displayed in HEX into a DECIMAL value
- Execute a SQL query to query SQL Server backup tables to determine the log
Step 1) Calculating SQL Server tlog based on LSN
Here is a short description of how to prepare a SQL query to determine which SQL Server backup to send to us:
First convert the 3 hex part LSN into decimal
Example: Current LSN= N’00010133:00004a7e:0001′
Split the LSN on the ‘:’ and convert them to decimal
hex 00010133 = decimal 65843
hex 00004a7e = decimal 19070 + left pad zero until it has length 10 = 0000019070
hex 0001 = decimal 1 + left pad zero until it has length 5 = 0001
The result in decimal is: lsn = 65843000001907000001
Use that number for <lsn> in the following query:
Step 2) Execute a SQL query to query SQL Server backup tables to determine the log
from backupset s join backupmediafamily f
on s.media_set_id = f.media_set_id
where s.database_name = ‘<database name>’ and s.type = ‘L’
and <lsn> between first_lsn and last_lsn
The result of the query identifies which backup log needs to be recovered.
- You must be logged in to reply to this topic.