Failed to get LSN data from server

We are using Red-Gate SQL Backup V4.1.207 and getting this error - not every time about three instances on tran log backups over the last 6 weeks or so for different databases. About 4 TRAN backups run concurrently and all usually take less than a second.

When this happens, the insert into msdb..backupmediafamily does not happen and we end up with incorrect information regarding the physical file name. This in turn is causing our custom built log shipping to fail on restore at the standby.

There is nothing corresponding with this in the VDI.log file

Has anyone else experienced this or know if it is fixed in a later version?

===============================================
Attempting to make a backup of database PMPOcean [SQLSTATE 01000]
Database PMPOcean status: ONLINE [SQLSTATE 01000]
Backup type to create: tlog [SQLSTATE 01000]
Destination folder: D:\MSSQL\BACKUP\Translogs [SQLSTATE 01000]
Creating tlog backup of datatabase PMPOcean to D:\MSSQL\BACKUP\Translogs\tlog_PMPOcean_200610111430.bak [SQLSTATE 01000]
Backup command: -SQL "BACKUP LOG [PMPOcean] TO DISK = 'D:\MSSQL\BACKUP\Translogs\tlog_PMPOcean_200610111430.TRN' WITH NO_TRUNCATE, COMPRESSION = 1" -E' [SQLSTATE 01000]
SQL Backup (DLL v4.2.0.511)
Backing up PMPOcean (transaction log) to:
D:\MSSQL\BACKUP\Translogs\tlog_PMPOcean_200610111430.TRN

Backup data size : 1.125 MB
Compressed data size: 66.000 KB
Compression rate : 94.27%

Processed 48 pages for database 'PMPOcean', file 'PMPOcean_log' on file 1.
BACKUP LOG successfully processed 48 pages in 0.006 seconds (65.280 MB/sec).
Error 742: Failed to get LSN data from server (0 rows returned). ()

SQL Backup exit code: 742
name value

exitcode 742
sqlerrorcode 0
filename01 D:\MSSQL\BACKUP\Translogs\tlog_PMPOcean_200610111430.TRN
Error performing LOG backup of database PMPOcean [SQLSTATE 01000]
Jim Kane
DBA
PMP Limited (Australia)

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup tries to retrieve the LSN information from the backupset table to insert into the backup file's header. It then updates the backupmediafamily table with the actual physical file name. Both these operations are independent i.e. if the search for the LSN data fails, the update of the backupset table still proceeds. What they both have in common is that they are searching/updating based on the virtual device name.

    In this case, it appears that the virtual device name as expected by SQL Backup does not match up with the actual value in the backupmediafamily table. Are the values in the physical_device_name field filled, in those cases where the LSN retrieval failed?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter,

    There are 2 entries in backupset for the same media_set_id. One relates the failed backup described above (and does contain the LSN data), the other to an older backup. There is only one entry in backupmediafamily which relates to the older backup. This is the "incorrect" file name that I am referring to. So in answer to your question, it's more like the entry is missing not that the physical device name is incorrect.
    Jim Kane
    DBA
    PMP Limited (Australia)
  • peteypetey Posts: 2,358 New member
    That is strange. Is there a pattern to when SQL Server reuses the media_set_id for the backups? Are there any entries in the backupmediafamily table that has a guid-like value in the physical_device_name field?

    Are you using sp_delete_backuphistory to maintain the backup history tables?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.