Error 760: LSN data from server is blank

steven@ddlsteven@ddl Posts: 6
edited November 27, 2007 10:49AM in SQL Backup Previous Versions
Hi We occasionally get this error message when the transaction log backups are running.
Multiple databases are being backed up from a single job. The transaction log appears to have been created OK even when this error message is displayed. SQL Backup version is 5.2.0.2825 backing up databases on SQL Server 2000. The subsequent transaction log at the next scheduled interval is created OK without any errors and no intervention.

Any help appreciated.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    When SQL Backup completes a backup, it queries the SQL Server backup history tables in the msdb database to retrieve backup details. It then writes these details into the backup files' header.

    The error message indicates that SQL Backup failed to retrieve the backup information from the msdb tables. The backup file is complete, its the file header that will be missing some information. This information is required if you plan to perform multiple log restores, as the LSN details are used to sort the files.

    Could you pls run the following query, and see if there are any rows returned:
    SELECT a.backup_start_date, a.database_name, a.first_lsn, b.*
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
WHERE b.physical_device_name LIKE 'SQLBACKUP_%'

    Are these the backups that encountered the error? Are there values in the first_lsn column?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steven@ddlsteven@ddl Posts: 6
    Hi,
    thanks for your prompt response.
    I ran the query you supplied. It returned 8 rows for two different databases with dates ranging from 03 Nov 2007 to 24 Nov 2007. These rows related to warning messages that we had received when the the transaction log backup seemed to fail first time but ran OK on a re-try.
    All of the rows returned with this query have a value in the first_lsn column.
    None of the databases where we have received an error report (Error 760: LSN data from server is blank) appear in the results from running this query.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please post the logs from one of the backups that failed on the first attempt, but subsequently succeeded? Were the backups that initially failed, and the backups which raised error 760, part of the same backup run? Lastly, were there any SQL Server error messages reported in the backups that raised error 760?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steven@ddlsteven@ddl Posts: 6
    Hi
    I have posted the first of the warning error messages that we received. Only the mail to data has been removed. The backups that generated the warning messages were not in the same run as the backups that generated the error messages. They are in the same job but did not occur at the same time. There were no messages in SQL Server logs relating to the warning or the error messages. I have listed the warning message first then the error message (different database but on the same server), then the entry from the SQL Server Log for the same period as the error log was generated for SQL Backup.

    Further info: We also have a second server where we have received a couple of warning messages using SQL Backup. The Backup software for both servers is maintained from a single server.

    03/11/2007 11:30:23: Backing up eluciddb (transaction log) to:
    03/11/2007 11:30:23: d:\sqlbackup\redgate\LOG_(local)_eluciddb_20071103_113023.sqb

    03/11/2007 11:30:23: BACKUP LOG [eluciddb] TO DISK = 'd:\sqlbackup\redgate\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', VERIFY, ERASEFILES = 2, MAILTO = '', COMPRESSION = 3

    03/11/2007 11:30:53: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.) 03/11/2007 11:46:44: SQL error 3013: BACKUP LOG is terminating abnormally. 03/11/2007 11:46:44:
    03/11/2007 11:46:44: Memory profile
    03/11/2007 11:46:44: Type Maximum Minimum Average Blk count Total
    03/11/2007 11:46:44:
    03/11/2007 11:46:44: Commit 769785856 4096 1679447 1079 1812123648
    03/11/2007 11:46:44: Reserve 4128768 8192 397711 123 48918528
    03/11/2007 11:46:44: Free 166141952 4096 938937 305 286375936
    03/11/2007 11:46:44: Private 769785856 4096 3363406 534 1796059136
    03/11/2007 11:46:44: Mapped 1536000 4096 121560 149 18112512
    03/11/2007 11:46:44: Image 8593408 4096 90309 519 46870528
    03/11/2007 11:46:44:
    03/11/2007 11:46:44: Warning 300: Backup failed. Retry attempt: 1 03/11/2007 11:46:46: BACKUP LOG [eluciddb] TO DISK = 'd:\sqlbackup\redgate\LOG_(local)_eluciddb_20071103_113023.sqb' WITH NAME = 'Database (eluciddb), 03/11/2007 11:30:23', DESCRIPTION = 'Backup on 03/11/2007 11:30:23 Server: DBDIRECT_NT5 Database: eluciddb', INIT, VERIFY, ERASEFILES = 2, MAILTO = 'xxx@x.com', COMPRESSION = 3

    03/11/2007 11:46:50: Backup data size : 2.938 MB
    03/11/2007 11:46:50: Compressed data size: 465.500 KB
    03/11/2007 11:46:50: Compression rate : 84.52%

    Processed 236 pages for database 'eluciddb', file 'eluciddb_install_Log' on file 1. BACKUP LOG successfully processed 236 pages in 0.920 seconds (2.099 MB/sec). 03/11/2007 11:46:50:
    03/11/2007 11:46:50: Validating files:
    03/11/2007 11:46:50: d:\sqlbackup\redgate\LOG_(local)_eluciddb_20071103_113023.sqb
    03/11/2007 11:46:50:
    03/11/2007 11:46:50: RESTORE VERIFYONLY FROM DISK = 'd:\sqlbackup\redgate\LOG_(local)_eluciddb_20071103_113023.sqb'

    The backup set is valid.
    03/11/2007 11:46:51: Deleting old backup file: d:\sqlbackup\redgate\LOG_(local)_eluciddb_20071101_113031.sqb
    03/11/2007 11:46:51: Deleting old backup file: d:\sqlbackup\redgate\LOG_(local)_eluciddb_20071101_114513.sqb


    Error log from SQL Backup
    26/11/2007 15:16:04: Backing up SIT (transaction log) to:
    26/11/2007 15:16:04: d:\sqlbackup\redgate\LOG_(local)_SIT_20071126_151604.sqb

    26/11/2007 15:16:04: BACKUP LOG [SIT] TO DISK = 'd:\sqlbackup\redgate\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', VERIFY, ERASEFILES = 2, MAILTO = '', COMPRESSION = 3

    26/11/2007 15:16:05: Backup data size : 512.000 KB
    26/11/2007 15:16:05: Compressed data size: 5.000 KB
    26/11/2007 15:16:05: Compression rate : 99.02%

    BACKUP LOG successfully processed 0 pages in 0.015 seconds (0.000 MB/sec). 26/11/2007 15:16:12: Error 742: Failed to get LSN data from server (0 rows returned). () 26/11/2007 15:16:12: Error 760: LSN data from server is blank.


    SQL Server backup message from the log file for the SIT backup for the same backup run:
    Log backed up: Database: SIT, creation date(time): 2004/05/06(15:24:51), first LSN: 290:16335:1, last LSN: 290:16335:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_883EC829-647E-43C9-9D8F-9A4BBD56D8B0'}).
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Re the backup retries, it appears that the log backup was blocked by another process. Once the blocking process completed (after 16 minutes), SQL Server then attempted to perform the backup, by which time SQL Backup had already timed out, so SQL Backup reattempted the backup. The physical_device_name column in msdb..backupmediafamily should have been updated with the backup file name. Are there any rows returned if you run the following:
    SELECT * FROM msdb..backupset a 
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'eluciddb'
  AND a.type = 'L'
  AND a.backup_start_date &gt;= '3-Nov-2007 11:46'
  AND a.backup_finish_date &lt;= '3-Nov-2007 11:47'
    Re error 760, for that particular backup you posted, there were no data backed up. However, SQL Server should still have created an entry in the backup history tables. Are there any rows returned if you run the following:
    SELECT * FROM msdb..backupset a 
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = 'SIT'
  AND a.type = 'L'
  AND a.backup_start_date &gt;= '26-Nov-2007 15:16'
  AND a.backup_finish_date &lt;= '26-Nov-2007 15:17'
    For all the backups where error 760 was raised, were they all backups with 0 pages processed?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steven@ddlsteven@ddl Posts: 6
    Hi
    My apologies: We did have an error message for the warning log that I included. I didn't realise as the messages were sorted by message so this did not appear in the list where I was looking for it.

    BACKUP failed to complete the command BACKUP LOG [eluciddb] TO VIRTUAL_DEVICE = 'SQLBACKUP_DF533258-0618-4D56-AE18-76C69D807A42' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (eluciddb), 03/11/2007 11:30:23', DESCRIPTION = N'Backup on 03/11/2007 11:30:

    I have ran the first script that you supplied and can confirm that we have an entry in the backupset table for this and confirmed it matched the pyhsical_device_name in the SQL Server log.

    I ran the second script which also returned one record but the entry in the pyhsical_device_name field actually referred to the location of the old backup files (before we started using red-gate). The values in the name and description fields refer to the current backup.

    We have only had two backups failed due to 760 errors and on both occassions the pages processed was 0
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Re the first issue, are you saying that there is an entry in the msdb..backupmediafamily table with a physical_device_name value of 'SQLBACKUP_DF533258-0618-4D56-AE18-76C69D807A42'?

    Re the 2nd issue, I do not know why SQL Server referenced the old file. Is there any other entry in the msdb..backupset table that has the same media_set_id value?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steven@ddlsteven@ddl Posts: 6
    Hi
    Sorry for any confusion caused.
    Re point 1:
    We do not have an entry in the backupmediafamily where physical_device_name = 'SQLBACKUP_DF533258-0618-4D56-AE18-76C69D807A42'. The error message I was refering to was displayed in the SQL Server logs.

    Re point 2:
    I have provided an extract of the data from the query you provided (amended) where the column values were different between the backups that passed or failed

    SELECT a.name, substring(a.user_name,1,8), substring(b.logical_device_name,1,33), b.physical_device_name, b.device_type, b.physical_block_size
    FROM msdb..backupset a
    INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
    WHERE a.database_name = 'SIT'
    AND a.type = 'L'
    AND a.backup_start_date >= '26-Nov-2007 14:16'
    AND a.backup_finish_date <= '26-Nov-2007 15:40'


    Database (SIT), 26/11/2007 14:30:58 DBDIRECT Red Gate SQL Backup (5.2.0.2825): d:\sqlbackup\redgate\LOG_(local)_SIT_20071126_143058.sqb 7 65536
    Database (SIT), 26/11/2007 14:46:02 DBDIRECT Red Gate SQL Backup (5.2.0.2825): d:\sqlbackup\redgate\LOG_(local)_SIT_20071126_144602.sqb 7 65536
    Database (SIT), 26/11/2007 15:01:16 DBDIRECT Red Gate SQL Backup (5.2.0.2825): d:\sqlbackup\redgate\LOG_(local)_SIT_20071126_150116.sqb 7 65536
    Database (SIT), 26/11/2007 15:16:04 sa NULL D:\SQLBACKUP\GlobalTest\GlobalTest_tlog_200612201110.TRN 2 512
    Database (SIT), 26/11/2007 15:31:29 DBDIRECT Red Gate SQL Backup (5.2.0.2825): d:\sqlbackup\redgate\LOG_(local)_SIT_20071126_153129.sqb 7 65536

    I have checked the tables below and both queries return a single row.
    select * from msdb..backupmediafamily where media_set_id = 255147
    select * from msdb..backupset where media_set_id = 255147
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please send me an email, via my profile? I need to clarify some things, and I think it's best to conduct it offline as it's getting rather confusing. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.