April 21, 2009 at 6:31 am
Morning folks...
I am trying to determine the source of an error I see each morning in my SQL logs. The error indicates a backup device is out of space, etc... standard stuff. So I am trying to trace the source of this backup job. I inherited this particular SQL server. I have my own Maint Plans which perform backups, cleanup, index rebuilds, etc... however, I do not see any other Agent jobs nor do I see any Maint Plans nor legacy dts packages via SSMS on this server.
I checked the "backup" tables in MSDB to see if there are any clues but found none.
I even checked scheduled tasks on that server... there are a few jobs but no SQL backups.
Please advise on some methods to trace the source of these failed backups.
Thanks,
Nick
April 21, 2009 at 6:35 am
could be a third party backup tool. If the backup has never completed there would be no record in msdb.
whats the exact error?
---------------------------------------------------------------------
April 21, 2009 at 6:51 am
Hmm... I'll check for third party tools, thanks.
Here is the full error from the log:
BackupMedium::ReportIoError: write failure on backup device '\\servernameremoved\sql bacups\BEACH\Amazon_backup.bak'. Operating system error 112(There is not enough space on the disk.).
This particular db / application uses some stored procs to create backups at certain points; however, those are writing to another location. I can take a look at the sprocs to see if there are any others that perform backups.
However, I have the same issue with another db on this server pointing to the saem backup location. That's what leads me to believe it is part of some sort of job.
Thanks again.
April 21, 2009 at 7:11 am
You have to be careful if this is a mainenance plan
I have seen this a few times and it has often been that the attempt to backup occurs before the old backup is removed. Therefore if you keep one backup, SQL will create the second one before removing the first.
Of course, that means that for a few seconds before the old one is removed you need about double the space.
Sometimes the old one is removed even though the new one is not created OK so when you look later there seems to be loads of space.
So you delete the incomplete one, it works fine the next time it runs (there is no backup to delete) but the time after that it fails.
Also check the exact cleanup parameters on the maintenance plan if any.
E.g. If set to 1 day it may keep an extra backup if the purge job runs 23 hours and 59 minutes later than the previous days run, but work OK if it runs 24 hours and 1 minute later.
.
April 21, 2009 at 7:14 am
that doesn' sound like an error from a third party enterprise tool.
This thread lists some ways to search for strings in your stored procs:
http://www.sqlservercentral.com/Forums/Topic695413-357-1.aspx#bm695503
---------------------------------------------------------------------
April 21, 2009 at 7:34 am
Thanks... I was just searching for a way to search through all my stored procs. I will look and get back to this post.
Nick
April 21, 2009 at 8:46 am
Well, I looked through stored procs of interest to no avail.
My next step is to set up a server side trace during the time when the errors are being generated. I'm hoping this will provide some more detail.
April 21, 2009 at 9:34 am
A trace is probably your best bet here. If you know the time, FileWatcher, from Sysinternals, can also help you trace down the accounts trying to do the access. That might provide a clue as well.
April 21, 2009 at 10:52 am
just as an aside you can have a trace at the particular time when you get this error, this will help you to reduce overhead on servers if you are having any. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply