May 14, 2012 at 1:24 pm
Hi,
We have 14-15 databases that get transaction log backups every 1/2 hour. They are backed up to a local drive - not C:/, but the same drive the data files are on. Each database has its own backup folder. All's been well for 9+ months, but in the last week or two we're getting failures:
Executing the query "BACKUP LOG [xxxx] TO DISK = N'F:\\MSSQL.1\\MSSQL\\Backup\\xxxx\\xxxx_backup_201205140830.trn' WITH NOFORMAT, NOINIT, NAME = N'xxxx_backup_20120514083001', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "Cannot open backup device 'F:\\MSSQL.1\\MSSQL\\Backup\\xxxx\\xxxx_backup_201205140830.trn'. Operating system error 5(Access is denied.).
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
When this happens, there'll be a zero byte file created in the directory. The backup 1/2 hour before and 1/2 hour after will go just fine. The specific database that fails is random - not always the same one(s). Some days none will fail, some days 3 or 4 will fail over the entire day. I've never seen two different databases fail at the same time, or the same one fail twice in a row. Not running especially tight on disk space. Server was rebooted for a third-party application update, still seeing failures.
Been Googling but no concrete answers. Where should I look next?
Thanks,
Pat
May 15, 2012 at 10:51 am
I've hit similar issues in the past. What we found was that there was a system backup software that was monitoring the files and reading them as we created them. It would prevent proper access from SQL Server, but it was random & intermittent. I'd look for something similar. Also, see if any new software has been installed or if any system changes have been introduced recently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2012 at 1:13 pm
Thanks, Grant. There was another backup software app installed, but not actually working-as far as I could tell-on this server as well as one or two others. I only knew it was there because of errors it threw that ended up in the event log (my boss inherited the system with about an hour's worth of training from his predecessor, and I came in about 6 months later - we're still discovering new and exciting issues every so often!). I have shut it off - or at least the service I could find. I'll look around at the startup programs and see if there's something there, too.
Just strange that it should start now, after many months of running OK. Oh well, at least it's emailing me when it errors out, that's working well 🙂
Thanks again!
Pat
May 15, 2012 at 1:21 pm
The fact that it's intermittent suggests that it may have been running, but you were just missing the windows. Our situation was the same. It actually went away twice on it's own before we figured out what the issue was.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply