question

RafaelPonce-6620 avatar image
0 Votes"
RafaelPonce-6620 asked singcheong edited

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

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.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered RafaelPonce-6620 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monitored (with PRTG) the server network service and not even with packet loss.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @RafaelPonce-6620,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered

Hi @RafaelPonce-6620,

Is the reply helpful?

BR,
Mia


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RafaelPonce-6620 avatar image
0 Votes"
RafaelPonce-6620 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

singcheong avatar image
0 Votes"
singcheong answered singcheong edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.