Log Shipping Copy Job Fails once in a while

Question

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.

Answer 1

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.

Answer 2

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.