1

I have configured log shipping at multiple database server with monitored server(separate server) and all of them are running fine without any issues.

One particular server has 7 database and the log shipping copy job fails once in a while for all databases, when I checked the error message, every time error remains same:

*** Error: The network path was not found.
(mscorlib) ***

This issue is not persistent and occurs at random time and at any given day(not every day). Below is details of log shipping schedule:

  1. Log backup job is scheduled to run every 15 minute at primary server(file is generated at monitoring server).
  2. LS Copy job is scheduled to run every 15 minute at secondary server, at the gap of 5 minute from log backup job.
  3. LS restore job is scheduled to run at 15 minutes at secondary server, at the gap of 5 minutes from LS copy job.

Above jobs are having delay of 5 minutes considering network speed and file size.

Can anyone please suggest what could be the underlying issue.

For time being, I have asked Infra admin to add server name along with its IP in the hosts file at secondary server, don't know if this will solve the issue.

Note: Version of Operating System and Database is same at primary, monitoring and secondary server.

Job failed again for all databases pertaining to the same server and file didn't get copied. No details on the error message and very next run which was scheduled 15 minutes later got succeeded. I have no clue on this.

I think there is something wrong with server setting. Any input will be highly appreciated.

  • I believe you are taking log backup on shared drive correct ?. How is connectivity between the servers do you see packet drop, seems like network issue to me. Also make sure SQL Server agent service account has full permission on shared path. Is your VM reliable where you have cerated shared path ? – Shanky Mar 12 at 7:07
  • All the database backups from different servers(with their databases) are copied to same monitoring server and there is no issue for any other server except this one. And also secondary servers are in same subnet. – Learning_DBAdmin Mar 12 at 7:11
  • How big is this database whose copy is failing ?. Is it larger than other databases. Is this the same database copy of which fails every time ? – Shanky Mar 12 at 7:23
  • @Shanky These database backup vary in size from 6KB - 5MB. While the database server which hasn't failed until now, has average size of 165MB. – Learning_DBAdmin Mar 12 at 8:11
  • @Shanky Yes, same set of databases belonging to one server fails. – Learning_DBAdmin Mar 12 at 11:53
0

This is not SQL-Server error actually,

This occurs when the LS Copy Job tries to copy the file on destination location(On Shared Network location) Now, when the location is not available due to temporary connection loss with network, or due to any reason. (when its connected through internet & VPN reasons can be multiple)

So this can be broadly concluded as Network path was not reachable(not available at that time)

Solution: Check that if network location available before copy. If network location reachable then only Init copy.

I hope this answers what you are looking for.

  • Thank you for your answer however it doesn't look like network issue to me as others jobs are not failing. All jobs are keeping their backup at same monitoring server and while copying job triggers from secondary server - they are in same subnet. If it was network issue, all jobs should have failed why only jobs fail pertaining to this server only? – Learning_DBAdmin Mar 12 at 7:13
  • I have seen this error many times in my career, and have found the above as primary reason, or if any antivirus is blocking your copy operation. make sure your target location is not having any antivirus scan going on that folder. Solution that location should be added in exception. – MarmiK Mar 12 at 7:20
  • Connectivity of servers depends on the hardware connected to that server. If this specific server's hardware or cable have issues, may be your network guy can help you monitor that better. – MarmiK Mar 12 at 7:22
  • Could you please elaborate how would you ensure that network location is available before copying? Is there any option while configuring log shipping job? I don't suspect this is a hardware issue as its recently commissioned server and job doesn't fail frequently, it happens only once in a while and that too any time - there is no pattern. – Learning_DBAdmin Mar 12 at 8:17
0

After checking various blogs, system events, logs etc, we couldn't find anything unusual from database perspective and that's where I flagged it to Senior System administrator to check the issue as it was happening only for this server and not for any other server.

We found one more issue only for this server and that was - we were unable to login to this database server from SSMS without using non-default port whereas default port and one non-default port is configured at server level. For other servers, we didn't have any issue. They were working with host name or IP with/without port. Incoming firewall rule was defined for all the servers and even telnet was working as expected for all the servers. we even used tracert to check path followed from source server to destination server and no issue there.

Initially, Server admin was pointing towards network/database issue however since other jobs were not failing in the same subnet and it was confined to only this particular server, he started checking various details.

After digging at various levels, he found out difference between this server and other servers as SQL Server Service was registered in the Host SPN for the NetBIOS with the name of SQL Server along with port 1433, for other servers it was only server name and no port associated with that. Hence, it was kind of free to take any port defined at SQL server Configuration Manager. He deleted this SPN and restarted secondary server and connection started working from SSMS to this server similar to other servers i.e. using only host name or IP with/without port number.

Now, after this is in place - we are assuming that log shipping will also work smoothly and should not fail intermittently as copy job is inside MSSQL, so this could be one reason for the failure. I am not very sure, if this will fix underlying issue or not however I am posting this as an answer and I am open to have any suggestion on this. I will wait for at least one month before I accept this as an answer, which solved my issue.

This job failed last Friday night with the same reason, clueless again. Need to set up meeting with Infra admin again to check any issue.

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.