How to identify the SQL Server TLog backup log file based on the LSN

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #16235
    ggoodrich
    Keymaster

    How To:  identify the SQL Server TLog backup log file based on the LSN

    Scenario:

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

    Steps:

    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

    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

    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

    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.

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us