Activity overview
Latest activity by bsantosh6
the service account running sqlbackup is sysadmin btw / comments
the service account running sqlbackup is sysadmin btw
Some Progress!
I ran the the script you provided and it retuned the result :
backup_start_date ,backup_finish_date ,logical_device_name ,physical_device_name
2012-04-24 02:18:09.000 ,2012-04-24 02:18:11.000 ,NULL, R:\ShippedLog\3184\PB_0605_3184_tlog_201011301900.TRN
This is pointing to a wrong physical_device_name!
Some more research and querying and I see that when the job runs as 'sa' it has no issues.But when it runs as the "service account that SQLBACKUP service runs under" ..it fails...!
I went back almost 3 months in backup history and saw this pattern.
I used this query to confirm the pattern:
SELECT user_name,database_name,server_name,logical_device_name,physical_device_name,device_type
FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE user_name like '%SVC%'
AND a.type = 'D'
AND a.backup_start_date >= '01-Jan-2012 01:00' AND a.backup_finish_date <= '25-Apr-2012 04:00'
ORDER BY a.backup_start_date
--results show all my failures and in all these rows the physical_device_name is wrong....redgate log confirm these exact failures where the 760 error was thrown...
On Running this:
SELECT user_name,database_name,server_name,logical_device_name,physical_device_name,device_type
FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE user_name like 'sa'
AND a.type = 'D'
AND a.backup_start_date >= '01-Jan-2012 01:00' AND a.backup_finish_date <= '25-Apr-2012 04:00'
ORDER BY a.backup_start_date
--all good backups and correct physical_Device_name! / comments
Some Progress!
I ran the the script you provided and it retuned the result :
backup_start_date ,backup_finish_date ,logical_device_name ,physical_device_name
2012-04-24 02:18:09.000 ,2012-04-2...
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 / comments
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.med...
-Firstly,thank you! thats good news.I am somwhat relieved [image]
-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 / comments
-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 alway...
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????? / comments
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 th...
-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? / comments
-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 questi...
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) / comments
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 datafile...
SQL Backup failed with exit code: 760
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 fi...