March 17, 2015 at 1:23 pm
This issue has existed for a long time. SQL Server 2005 SP2 on server 2003.
We use SQL Server maintenance plans to back up tlogs every 3 hours from 4am to 8pm every day.
We do a full backup every day at 130am.
also at 130am we do a maintenance cleanup to delete history, and then delete backups older than 5 days.
This works fine Monday-Saturday, but every Sunday morning at 7am or 8am depending on daylight savings time, the tlog backup for the main db fails. That backup fails every three hours into the next day. Then Monday morning the tlog backups start working again.
The error for the tlog failure is:
2015-01-11 10:00:02.08 Backup Error: 3041, Severity: 16, State: 1.
2015-01-11 10:00:02.08 Backup BACKUP failed to complete the command BACKUP LOG PFM. Check the backup application log for detailed messages.
2015-01-11 10:00:02.33 spid62 Error: 18204, Severity: 16, State: 1.
2015-01-11 10:00:02.33 spid62 BackupDiskFile::OpenMedia: Backup device 'G:\PFM_TLog_Backups\PFM\PFM_backup_201501111000.trn' failed to open. Operating system error 2(The system cannot find the file specified.).
This is not a disk space issue as far as I know, we have plenty of disk, and this failure only occurs sundays.
Also this past weekend we got an alert that the 130am Monday morning full backup failed, but the error log shows everything completed.
I've checked all logs I can think of except the actual transaction logs. I've analyzed the scheduled jobs and don't see a conflict. Anyone here have any tips? I'm going to look into setting up a profiler now.
This is my first issue as the new DBA here and I really want to knock this issue out.
March 17, 2015 at 2:45 pm
add2700 (3/17/2015)
This issue has existed for a long time. SQL Server 2005 SP2 on server 2003.
First thought is that fixing the ageing OS/SQL Version should be a higher priority than the log backups on Sundays
We use SQL Server maintenance plans to back up tlogs every 3 hours from 4am to 8pm every day.
We do a full backup every day at 130am.
also at 130am we do a maintenance cleanup to delete history, and then delete backups older than 5 days.
This works fine Monday-Saturday, but every Sunday morning at 7am or 8am depending on daylight savings time, the tlog backup for the main db fails. That backup fails every three hours into the next day. Then Monday morning the tlog backups start working again.
The error for the tlog failure is:
2015-01-11 10:00:02.08 Backup Error: 3041, Severity: 16, State: 1.
2015-01-11 10:00:02.08 Backup BACKUP failed to complete the command BACKUP LOG PFM. Check the backup application log for detailed messages.
2015-01-11 10:00:02.33 spid62 Error: 18204, Severity: 16, State: 1.
2015-01-11 10:00:02.33 spid62 BackupDiskFile::OpenMedia: Backup device 'G:\PFM_TLog_Backups\PFM\PFM_backup_201501111000.trn' failed to open. Operating system error 2(The system cannot find the file specified.).
This is not a disk space issue as far as I know, we have plenty of disk, and this failure only occurs sundays.
Also this past weekend we got an alert that the 130am Monday morning full backup failed, but the error log shows everything completed.
I've checked all logs I can think of except the actual transaction logs. I've analyzed the scheduled jobs and don't see a conflict. Anyone here have any tips? I'm going to look into setting up a profiler now.
This is my first issue as the new DBA here and I really want to knock this issue out.
Quick questions,
1) do you have any other jobs which are scheduled for Sundays such as DBCC CHECKDB etc.?
2) have you considered using the likes of Ola Hallengren's maintenance scripts rather than the Maintenance plans, strongly recommend you look into that, most DBA's I know prefer those?
You can adjust the logging of the maintenance plan's tasks as these are simply SSIS packages, might give you an insight into what is happening.
😎
March 17, 2015 at 3:34 pm
Log into your server sometime on sunday while the error is occuring and verify that the backup location is actually available 🙂
March 19, 2015 at 8:33 am
Eirikur Eiriksson (3/17/2015)
add2700 (3/17/2015)
This issue has existed for a long time. SQL Server 2005 SP2 on server 2003.First thought is that fixing the ageing OS/SQL Version should be a higher priority than the log backups on Sundays
BAD ADVICE BAD ADVICE Don't wait for a new version to fix your backups!
Ok back to your regularily scheduled thread 🙂
March 19, 2015 at 9:13 am
patrickmcginnis59 10839 (3/19/2015)
Eirikur Eiriksson (3/17/2015)
add2700 (3/17/2015)
This issue has existed for a long time. SQL Server 2005 SP2 on server 2003.First thought is that fixing the ageing OS/SQL Version should be a higher priority than the log backups on Sundays
BAD ADVICE BAD ADVICE Don't wait for a new version to fix your backups!
Ok back to your regularily scheduled thread 🙂
My thought is that a 10 year old sql version and five years since the last service pack should probably be considered more of a problem, there is no waiting for a new version as there have been 13 (versions/sps) released since.
😎
March 19, 2015 at 9:25 am
Thanks for the info. I'll replace the maintenance jobs with better crafted ones.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply