SQL Server Backup Job failed multiple times

Question

I am getting the same error as mentioned by Mr.Ali

Sql Server Backup Job failed

Can anyone please explain the possible ways to fix this error as the backup is failing since 1 month.

Here is the Job history log

Executed as user: SAP\sqladmin.
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started:  21:00:00
Progress: 2014-10-03 21:00:05.83
Source: {E7436186-D15A-4485-92B4-7623DA7DDF4D}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete
End
Progress
Error: 2014-10-03 23:32:25.53
Code: 0xC002F210    
Source: Back Up Database Task Execute SQL Task
Description:
Executing the query "BACKUP DATABASE [P01] TO  DISK =N'\\cmgsap31.sap...."
failed with the following error:
"Write on "\\cmgsap31.sap.logica.com\p$\Central-MS-SQL-Backup\SAP0233_P01_Full.bkp"
failed: 64(failed to retrieve text for this error. Reason: 15105) 
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.". 
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
End Error 
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 21:00:00
Finished: 23:32:25
Elapsed:  9145.29 seconds.
The package execution failed.  The step failed.

Answer 1

Well based on Reason:15105 I think that SQL Server doesn't have permission to access to the path you check the permissions of the account that is assigned to the SQL service account? or other reason would be that there is not enough free space to create backup.

Answer 2

This is a network timeout issue. You can troubleshoot this with your network team or by running a network packet trace that is filtered to SMB traffic between the SQL Server and the destination file server. A word of caution, this trace will be intensive with the tools that I am recommending and I have had mixed success with them (I last used it in an environment where I did not have a dedicated network team) and can cause network problems so choose a high performance filter to perform these traces and proceed with caution. What you will likely see when you examine the capture is that during one of the final commands you run into a network latency issue that causes a timeout which results in the backup failure you see. To work around this you will need to tune the SMB shares to not timeout as quickly especially if you are dealing with a large backup and a slow network.

I've used NetMon in the past and had good luck at capturing this type of data. You will need to confirm the ports in use on your network but they should be UDP 137 and 138 and TCP 137 and 138 for SMB traffic. From here you will need to write a filter that captures only traffic that has a source of your SQL Server or the file share and destination of the same. I highly recommend some reading on high performance filters with NetMon as this utility does have overhead involved and can cause packet loss or odd performance issues which may result in an inability to reproduce the issue. I would also suggest a rolling capture as it will be unlikely that you require all of the data captured but instead just the last several frames.

There appears to be an updated version of NetMon which is now called Message Analyzer but I have no familiarity with this product so I cannot speak for any issues that may result or compatibility with the product.

I hope that I have provided you with at least a place to start and possibly some ideas for how to troubleshoot this issue. Given that you have a network stack in the middle there are any number of reasons why you may be seeing this problem. I would highly recommend that you seek out your network team if you have one and see if they can guide you further. Best of luck!

Answer 3

This issue usually occur if you have a third-party backups and it might be breaking Log chain. I have came across the same problem at my work. Everyday, there are working fine but we had a third party backup at 9 pm and the backups fail for next 4 hours, then 1 am diff backup is done fine. I resolved this issue by moving third party backups from Database and disk level.