Session ended before completion of execution - TCP Provider: The semaphore timeout period has expired

Question

Hi, I have been having a lot of problems with my SQL Server 2016 and I have not been able to find a solution.
The error occurs when executing any type of process like:

• Some process locally
• Execute process that uses linked server
• When running a Reporting Service report
• In scheduled tasks (JOBS)

This happens randomly, it can be on the first run or after the first run.

Example:

First run:
SELECT * FROM TABLE
Error:
Session Provider: Physical connection is not usable [xFFFFFFFF]
TCP Provider: The semaphore timeout period has expired

Second run:
SELECT *
FROM TABLE
Query executed successfully

Reviews I have made:

• The network connection is stable
• The execution times are not long, maximum 5 minutes
• The physical resources of the server are not saturated (disk, ram, cpu)

Has something similar happened to you? o What could be the cause?

Thanks in advance for the support.

Answer 1

I believe this is due to slow network connection between DB that runs the SSIS, each of the linked DB in the SSIS job design. If SSIS is diff machine, and there are 2 other DB involved, then there are total of 3 machine to check their network connectivity

Suggestion:

1. In each machine, checks "netstat -s" for each NIC card, and ensures no increase in "errors," "discard," "failure" related metrics
   E.g. if server uses IPv4, then focus in following section of netstat output

IPv4 Statistics

Packets Received = 8799877
Received Header Errors = 0
Received Address Errors = 0
Datagrams Forwarded = 0
Unknown Protocols Received = 0
Received Packets Discarded = 33505
Received Packets Delivered = 4614424
Output Requests = 4192937
Routing Discards = 0
Discarded Output Packets = 3145
Output Packet No Route = 45
Reassembly Required = 24
Reassembly Successful = 12
Reassembly Failures = 0
Datagrams Successfully Fragmented = 12
Datagrams Failing Fragmentation = 0
Fragments Created = 24

1. Captures before and after in each machine to narrow down which machine has network communication issue
2. Ensure all 3 machine has sufficient CPU, RAM, disk
3. Ensure all 3 machine has no virtual memory paging to disk
4. Creates simpler SELECT INTO <TABLE-Target> FROM <TABLE-Source> across 2 DB with table size that is 10MB, 100MB, 1MB, and determine how sensitive and frequent to simulate the network instability/delay issue
5. Creates long running SQL query that takes 5, 10, 15, 30, 1 hr to return its result back into SSIS. Such as SELECT COUNT(1) FROM <Big-Table> WHERE <time consuming filter> to simulate the TCP low level session force-close configuration along the network

Under extreme case, option:

1. Moves all 3 machines to the same network. This will eliminates many hops, e.g. VPN, firewall, router, switches
2. Moves all 3 machines to the same physical network switch
3. Installs all 3 DB into the same machine. This will eliminate entire network all together, and don't bother with any network equipment and impact

Answer 2

I did not find a solution for this case, my solution was to migrate the databases to another server.

Answer 3

Hi @Rafael Ponce ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi @Rafael Ponce ,

Do you find any exceptions and error messages in the errorlog? View error log as this: viewing-the-sql-server-error-log

Ping each other's IP address and ping each others' FQDN, test whether they all can be ping successfully.

Run cmd as Administrator in your server and then input your linked server's ip and port as next when the issue occurs, and together with your IT using wireshark to cpture:

Code1:

telnet IPaddress port  

Code2:

select * from processes  

I doubt whether other process or connection used the port or the network rate is low,or some other things in the netwoek such as broadcasting or what ever other connections blocked the port and then trigger the timeout.

Similar issue:tcp-provider-the-semaphore-timeout-period-has-expired

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

I'm with Tom. This is a network/communications problem of some sort. Could be an evil security sniffer on the network as well.

What environment is this? At home? Corporate? Any VPN or other remote connection?

Ping maybe fine, but these could be very short glitches.