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

That error is a communication error with the server. It must be either a network issue or the server disconnected your session.

Answer 2

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.

Answer 3

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 4

Hi @Rafael Ponce ,

Is the reply helpful?

BR,
Mia

---

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

Answer 5

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