December 28, 2010 at 3:52 pm
Hi,
We have backup job which is failing with the below message only some times and it's running fine most of the times.
spid64 BackupDiskFile::RequestDurableMedia: failure on backup device '\\Backups\Mydb.bak'. Operating system error 64(The specified network name is no longer available.).
What are events I need to select to run SQL profiler to get more details why the backup is failing?
Thanks
December 28, 2010 at 6:48 pm
I don't think Profiler is much help in troubleshooting backups. You can add an output file to your backup job and it may output more data for you to help determine what is going wrong.
I'm guessing though that a "hiccup" occurred on the backup media that appears to be a network share. If you have the space it is safer to backup locally and then use SSIS or script to move the files to your network share. SQL Server does not recover very well when it is trying to write over the network and something causes a packet or the connect to drop just for a second.
You might get your network shop to monitor the server connection to the share during backup times, it might show something. Your better option though is to backup local and move the backup to the network share.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 28, 2010 at 9:56 pm
Agree with Shawn. It is a good practice to backup the database locally and copy it to the network share later on. You can also schedule a job to copy using ROBOCOPY.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 28, 2010 at 10:15 pm
Profiler is not able to help you on it because it is not able to capture network related issue. You can view SQL Server error log.
Thanks
December 29, 2010 at 4:38 am
I tend to agree that it is best to backup locally first and then copy to a remote server. But if that option is not currently available to you, then you need to make sure the relevant service accounts have the neccessary permissions on the Network share.
Gethyn Elliswww.gethynellis.com
December 29, 2010 at 6:37 am
You can use Profiler to capture statement starts, but if a statement errors out, Profiler doesn't catch that completion event because it didn't fire. I agree with everyone else. I'd use the output to see what errors were generated during the backup process itself.
That said, you can use a server side trace to capture errors and exceptions. While I don't think it's the best approach, you can look at the "Errors and Warnings" group and capture the ErrorLog, EventLog, Exception, and User Error Message events. These should cover what you would get from the ouptut of the backup command itself. However, you're really smashing hard at the system with a brick when you could just use the scalpel.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply