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:

-.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)

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.

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.

-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

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?