3

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.
2
  • 1
  • @user51250, OS error 64 is "The specified network name is no longer available." There is something happens with the connection during the backup. Do you have enough space to make the backup to the local drive and then try copying to the remote location?
    – DenisT
    Oct 30 '14 at 12:48
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.

14
  • 1
    Thanks for the reply Anjana.I have checked the permissions of the user account. It has the access and there is 80+ GB free space in the disk so i think this may not be the problem as well. Any other possible reasons?
    – user51250
    Oct 30 '14 at 9:50
  • @user51250 Is it possible that there is problem accessing the network path? \\cmgsap31.sap.logica.com\p$\Central-MS-SQL-Backup\
    – Heisenberg
    Oct 30 '14 at 10:08
  • @user51250 Try Starting the SQL Management Studio "As Administrator."and execute the job may be its the fact that SQL Management Studio must be having a issue accessing the Path.
    – Heisenberg
    Oct 30 '14 at 10:20
  • Yes we are starting the SQL Management Studio as Administrator only, even though we are getting the same error. We tried accessing the path from our server it is working fine with no issues.Any OS related problems for this?
    – user51250
    Oct 30 '14 at 11:40
  • 1
    @user51250 you being able to access the network path is not the same as the SQL Server service being able to access the network path. Also if you have a flaky network with intermittent connection issues, just because you can connect now doesn't mean SQL Server could connect at that time (or stay connected throughout). Oct 30 '14 at 13:30
1

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!

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.