How To Identify the SQL Server TLog Backup Log File Based on the LSN

Question

How can I identify the SQL Server TLog backup file based on SCN?

Environment

HVR 5

Answer

This procedure is helpful when you are unsure of the specific SQL Server transaction log backup log file that needs to be produced for investigation.

For example, the error message below:

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

1. Convert the LSN three-part number displayed in HEX into a DECIMAL value.
2. Execute a SQL query to query SQL Server backup tables to determine the log.

Calculate 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. For 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 this number as <lsn> in the following query:

Execute a SQL query to query SQL Server backup tables to determine the log

use msdb go select s.backup_set_id, s.first_lsn, s.last_lsn, s.database_name, s.type, f.physical_device_name 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 go

The result of the query identifies which backup log needs to be recovered.