SQL Backup failed with exit code: 760

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

SQL Backup failed with exit code: 760

Postby bsantosh6 » Wed Mar 21, 2012 4:10 pm

Hello,

--my backup files look ok (based on date modified and based on the jobs step output file )

--SQL BAckup however thows error 760 causing the job to reflect failed state.IN the step output file everything seems to success except the last line which says "Msg 50000, Sev 16: SQL Backup failed with exit code: 760 SQL error code: 0 [SQLSTATE 42000]"


--the tlog backups also fail with this error sometimes.(again,,,backup files look ok though)

--I ran this (that petey has given out in some other forum) :
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_%'


And it returns 39 rows....with backup start date showing dates beteen 2-4 years ago ( nothing recent)


---FYI..this is sql 2000 and SQL Backup v5.3.0.178


Any help would be appreciated!

thanks in advance!
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Thu Mar 22, 2012 9:18 am

msdb..backupset stores details of your backup sets. It should contain details of all your recent backups, not just those from 2 - 4 years ago. Is the drive where the msdb database is located running out of space? Could you please check the SQL Server error log if there are any entries related to SQL Server failing to update the backup history tables?

If you are out of space, you can remove old backup history entries using the sp_delete_backup_history stored procedure to free up some space e.g. the following deletes all entries made before 1-Jan-2012:

Code: Select all
msdb..sp_delete_backuphistory '1-Jan-2012'
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Re:SQL Backup failed with exit code: 760

Postby bsantosh6 » Thu Mar 22, 2012 4:44 pm

Firstly,Thanks for your response.I really appreciate it!

-.I checked the msdb file usage.its set to auto grow at 1 MB but unrestricted.So do you think the possible issue is that the msdb's datafiles tries to autogrow and sql_backup times out?any way to confirm this?

I have gone ahead an allocated an extra quater gig to the datafile.if this is indeed the issue...then we should not see these intermiitent failures...

(fingers crossed)


-running a msdb..sp_delete_backuphistory '1-Jan-2012' right away is not a feasible option for me now.Because a regular purge progess wasnt done and the msdb has grown to about 2GB(history tables having more than 3 mil rows! I have read in posts by MVPs that deleting this would have a huge performance impact on the system....So I ll keep this as the last option for now ( i will implement it during scheduled downtime though)
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Thu Mar 22, 2012 5:23 pm

So do you think the possible issue is that the msdb's datafiles tries to autogrow and sql_backup times out?any way to confirm this?

It's not SQL Backup that inserts data into the msdb tables, it's SQL Server itself. SQL Backup then tries to retrieve details of the backup from those tables, but it's returning empty. SQL Server would usually raise a warning when it cannot access the msdb tables. Were there any such warnings recorded in the SQL Backup log for those backup processes that raised error 760?

sp_delete_backuphistory is indeed slow on SQL 2000. Some options here. There's an alternative stored procedure you could use, or add a temporary index to the media_set_id column in the backupset table prior to running sp_delete_backuphistory.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Re:760 error

Postby bsantosh6 » Thu Mar 22, 2012 6:42 pm

-nope.no errors in sql logs.nothing about msdb full or msdb being inaccessible.


--thanks for the link that you sent.looks v interesting.i ll use that to set up a purge process asap.

Most imp question:

---as i said before...i see all my Dbs were backed up regardless of the 760 error...how does the 760 error affect me? Will it not allow me to restore the full backups + log backups?
Last edited by bsantosh6 on Fri Mar 23, 2012 7:46 pm, edited 1 time in total.
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Re:

Postby bsantosh6 » Thu Mar 22, 2012 10:07 pm

an update:

1) We backup about 400 DBs in one job ( sequentially).Used the sql backup logs to identify the individual db on which it was generating the 760 error.


2)ran 'restore sqbheaderonly" on the .sqb :

results:

Reading SQB file header of "E:\\MSSQL\\Backups\\troubleshooting_760_error\\PB_0605_3455\\FULL_(local)_PB_0605_3455_20120323_024035.sqb"
First LSN :
Last LSN :
Checkpoint LSN :
Differential base LSN :


LSN info empty!!!

3)converted the .sqb file to .bak file using the "Convert " option

4)ran std sql server restore header only on the .bak

Voila!!!


First LSN : 40905000000006000001

Last LSN : 40905000000005800001

Checkpoint LSN : 40905000000005800002

Differential base LSN :40905000000004900002



5)restored the DB on test env for the suspect DB

(full+tlogs)

(using both sqb and bak files)

all ok!
---------------------------------------------

So...the issue is most likely with sql backup?????
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Re:760 error

Postby petey » Sat Mar 24, 2012 12:18 pm

bsantosh6 wrote:how does the 760 error affect me? Will it not allow me to restore the full backups + log backups?

In each SQL Backup-created backup file, there is a header block that stores LSN details of the backup set. SQL Backup uses this information when you want to restore a sequence of transaction logs i.e. SQL Backup sorts the backup files in ascending LSN sequence, and restores each backup sequentially.

If this information is missing, SQL Backup would not be able to sort the files and would have problems restoring the transaction log files in the right order. You might then have to restore some backup files manually. Individually, each backup file can still be restored i.e. the missing LSN information does not affect the restorability of each backup file.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Re:

Postby bsantosh6 » Tue Mar 27, 2012 2:47 pm

-Firstly,thank you! thats good news.I am somwhat relieved :|

-I agree my backup history is huge..but thts mostly not the issue . I say this because the DBs on which the failure occur are not always in the Results of :
-------------------------------------------------------------------------
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_%'
---------------------------------------------------------------------------

--How do we fix this? Does this require a SQL Backup patch.It Looks like the issue is with my SQL backup.
This is my Sql backup ver : 5.3.0.178
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Wed Apr 18, 2012 5:33 pm

Could you please check in the SQL Server error log if any errors were recorded at the time SQL Backup reported error 760?
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Postby bsantosh6 » Wed Apr 18, 2012 6:40 pm

1)
Nope.No errors in the SQL log around that time.If fact I see this msg....

Database backed up: Database: xxxxxx, creation date(time): 2008/08/19(18:20:21), pages dumped: 1904, first LSN: 36692:212:1, last LSN: 36692:214:1, number of dump devices:
1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_E14F32C7-1F7E-4618-8F80-63F9A8EBCB05'}).


....which negates the 760 error(which indicated log info could not be retrieved).


2) This is what i see in the redgate logs:


----------------------------- ERRORS AND WARNINGS -----------------------------



4/18/2012 2:44:10 AM: Backing up xxxxxxxx (full database) to:
4/18/2012 2:44:10 AM: R:\\yyyyyyyyy\\xxxxxxxxx_20120418_024410.sqb

4/18/2012 2:44:10 AM: Deleting old backup file: R:\\yyyyyyyy\\xxxxxxxxx_20120417_023755.sqb
4/18/2012 2:44:10 AM: BACKUP DATABASE [xxxxxxx] TO
VIRTUAL_DEVICE = 'SQLBACKUP_E14F32C7-1F7E-4618-8F80-63F9A8EBCB05' WITH BUFFERCOUNT = 6, BLOCKSIZE =
65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (xxxxxxxxx), 4/18/2012 2:44:10 AM',
DESCRIPTION = N'Backup on 4/18/2012 2:44:10 AM Server: xxxxxxxx Database: xxxxxxxxx'

4/18/2012 2:44:11 AM: Database size : 267.563 MB
4/18/2012 2:44:11 AM: Compressed data size: 2.816 MB
4/18/2012 2:44:11 AM: Compression rate : 98.95%

Processed 1816 pages for database 'xxxxxxx', file 'PB_0605_0001' on file 1.
Processed 1 pages for database 'xxxxxxxx', file 'PB_0605_0001_log' on file 1.
BACKUP DATABASE successfully processed 1817 pages in 0.134 seconds (111.027 MB/sec).
4/18/2012 2:44:20 AM: Error 742: Failed to get LSN data from server (0 rows returned). ()
4/18/2012 2:44:20 AM: Error 760: LSN data from server is blank.

-----------------------------------------------------------------

3) I dunno if this help but:


-EXECUTE master..sqlbackup N' -SQL " RESTORE HeaderONLY.......

Works perfectly.Returns all LSN info for the db on which the failure occurred.



- EXECUTE master..sqlbackup N' -SQL " RESTORE SQBHeaderONLY.......

Returns all LSN as empty for the db on which the failure occurred.
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Thu Apr 19, 2012 2:57 am

Could you please run the following:

DBCC TRACEON (3004, 3605, -1)

This will cause SQL Server to log details of the backup process to the SQL Server error log. When you next run a backup that raises error 760, could you then post the corresponding entries from the SQL Server error log for that backup?

Thanks.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Re: Error 742, 760

Postby bsantosh6 » Tue Apr 24, 2012 2:31 pm

Per your suggetion,I did the TraceOn. I ve pasted below the data i see in the sql logs and red gate logs during the event of a failure:


Redgate logs:

----------------------------- ERRORS AND WARNINGS -----------------------------



4/24/2012 2:18:09 AM: Backing up PB_0605_3438 (full database) to:
4/24/2012 2:18:09 AM: R:\\MSSQL\\Backups\\FULL_BKUP\\PB_0605_3438\\FULL_(local)_PB_0605_3438_20120424_021809.sqb

4/24/2012 2:18:09 AM: Deleting old backup file: R:\\MSSQL\\Backups\\FULL_BKUP\\PB_0605_3438\\FULL_(local)_PB_0605_3438_20120423_021437.sqb
4/24/2012 2:18:09 AM: BACKUP DATABASE [PB_0605_3438] TO VIRTUAL_DEVICE = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (PB_0605_3438), 4/24/2012 2:18:09 AM', DESCRIPTION = N'Backup on 4/24/2012 2:18:09 AM Server: CLTEBIZBPSQLCL Database: PB_0605_3438'

4/24/2012 2:18:12 AM: Database size : 375.313 MB
4/24/2012 2:18:12 AM: Compressed data size: 45.069 MB
4/24/2012 2:18:12 AM: Compression rate : 87.99%

Processed 22240 pages for database 'PB_0605_3438', file 'PB_0605_0001' on file 1.
Processed 1 pages for database 'PB_0605_3438', file 'PB_0605_0001_log' on file 1.
BACKUP DATABASE successfully processed 22241 pages in 2.359 seconds (77.232 MB/sec).
4/24/2012 2:18:21 AM: Error 742: Failed to get LSN data from server (0 rows returned). ()
4/24/2012 2:18:21 AM: Error 760: LSN data from server is blank.





SQL Logs ( with traceON) :


2012-04-24 02:18:09.22 spid4240 BackupDatabase: Database PB_0605_3438
2012-04-24 02:18:09.22 spid4240 BackupDatabase: Streams open
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Checkpoint done
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Work estimates done
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Configuration section done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Database files done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Log files done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Final configuration done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: MBC done
2012-04-24 02:18:11.86 spid4240 BackupDatabase: Writing history records
2012-04-24 02:18:11.86 backup Database backed up: Database: PB_0605_3438, creation date(time): 2008/08/07(01:48:33),
pages dumped: 22336, first LSN: 52900:52:1, last LSN: 52900:54:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'}).

2012-04-24 02:18:11.89 spid4240 BackupDatabase: Finished
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Thu Apr 26, 2012 3:30 am

What is the result when you run the following in Query Analyzer:

Code: Select all
SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn,
   a.database_backup_lsn, a.media_set_id, c.name
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 = c.name
WHERE b.physical_device_name = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'
ORDER BY a.media_set_id DESC
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Re:760 error

Postby bsantosh6 » Thu Apr 26, 2012 2:54 pm

SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn,
a.database_backup_lsn, a.media_set_id, c.name
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 = c.name
WHERE b.physical_device_name = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'
ORDER BY a.media_set_id DESC


RESULT:

NULL
bsantosh6
 
Posts: 10
Joined: Wed Mar 21, 2012 3:13 pm

Postby petey » Thu Apr 26, 2012 4:21 pm

If you have not deleted the backup history records for the 24th, are there any entries in the msdb..backupset table for that backup? Try

Code: Select all
SELECT a.backup_start_date, a.backup_finish_date,  b.logical_device_name, b.physical_device_name
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
FROM msdb..backupset a
WHERE a.database_name = 'PB_0605_3438'
  AND a.type = 'D'
  AND a.backup_start_date >= '24-Apr-2012 02:00' AND a.backup_finish_date <= '24-Apr-2012 03:00'
ORDER BY a.backup_start_date
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2227
Joined: Sun Apr 24, 2005 12:34 pm

Next

Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 1 guest