SQL Server Msg 233 — "Transport-level error" Server-side or Network-side?
Diagnosing Msg 233: separating network drops from server-side terminations using ERRORLOG and sys.dm_exec_sessions, pool maxLifetime, TCP keepalive, and MTU.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)The same error message number appears with various underlying provider messages; the text inside the parentheses contains the primary diagnostic evidence:
(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
(provider: TCP Provider, error: 0 - The specified network name is no longer available.)
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Decoding the Provider Error Codes
- •121 — The semaphore timeout period has expired: The operating system TCP stack timed out waiting for an acknowledgment. The network path dropped: Stateful firewalls silently discarded an idle session, a VPN tunnel collapsed, NAT tables were flushed, or an MTU size mismatch caused unfragmentable packets to be dropped.
- •64 — The specified network name is no longer available: The remote host vanished: SQL Server terminated unexpectedly, or a virtual machine was frozen/stunned during an aggressive hypervisor migration (vMotion).
- •10054 — An existing connection was forcibly closed by the remote host: A TCP RST packet was received: A database administrator explicitly executed
KILL <SPID>, or an intermediate proxy/firewall aborted the circuit.
▸Diagnostic Step: Checking ERRORLOG for Server-Side Evidence
The defining hallmark of Msg 233 is that it often occurs on the client while leaving zero trace on the database engine. This absence of logs is itself definitive diagnostic proof:
EXEC sp_readerrorlog 0, 1, N'233';
EXEC sp_readerrorlog 0, 1, N'closed';- 1ERRORLOG Contains Nothing: The database engine never received the traffic or never recognized the drop. The failure is strictly located in the network infrastructure (firewalls, routers, VPNs, client-side sockets). Tuning database engine parameters will not fix the issue.
- 2ERRORLOG Contains Matching Entries: SQL Server attempted to transmit packets onto an already broken socket. Look for AlwaysOn Availability Group (AG) failover messages or resource exhaustion:
SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id;Inspecting Half-Open Connections
Does SQL Server still consider the session active?
SELECT session_id, login_name, host_name, program_name, status,
last_request_start_time, last_request_end_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY last_request_start_time DESC;If the client encountered Msg 233 but SQL Server still displays the session in a sleeping state, you are observing a classic half-open socket: The server has not yet detected the dead client. This is unequivocal proof that the OS TCP KeepAlive window is set too high.
▸Step-by-Step Resolution & Network Hardening
1. Retire Stale Connections in the Application Pool
The most frequent production cause: An application pool (ADO.NET, HikariCP) holds an idle connection for hours. An intermediate corporate firewall silently purges the state table entry without transmitting TCP RST packets. When the application leases the connection for a fresh request, it immediately encounters Msg 233. Remedy: Set the pool's Connection Lifetime / maxLifetime to be substantially shorter than the firewall's idle session timeout (e.g. 15 minutes if the firewall drops at 30 minutes).
2. Tune Operating System TCP KeepAlive Parameters
Windows defaults TCP KeepAlive to 2 hours (7,200,000 ms). Intermediate network devices purge idle entries within 5 to 30 minutes. Align OS keepalives on both client and database hosts via the registry:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters
KeepAliveTime = 300000 (DWORD, 5 minutes)
KeepAliveInterval = 1000 (DWORD, 1 second)3. Validate MTU and Packet Fragmentation
Over cloud VPC peerings and VPN tunnels, small packets (SELECT 1) succeed, while voluminous result sets freeze and fail with semaphore timeouts. Test for path MTU limits:
ping -f -l 1472 <sql-server-ip>If packets cannot be transmitted without fragmentation, lower the interface MTU accordingly.
4. Disable NIC Power-Saving Modes
Disable "Energy Efficient Ethernet" and power-saving profiles on physical and virtual NICs.
▸Related Error Guides & Hub Links
- •SQL Server Login Failed 18456 (H-04) — Authentication failures
- •SQL Server Connection Pool Timeout (H-08) — Pool exhaustion
- •SQL Server Msg 229 Permission Denied (H-39) — Object privilege denial
- •SQL Server Hub — High availability and AlwaysOn architecture
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
SQL Server Error 18456: Root Cause by State Code Matrix
In Error 18456, the State code reveals the actual root cause. Specific fixes for State 5, 8, 11, 18, 38, and 40, with ERRORLOG extraction scripts.
SQL Server Error 1205 Deadlock: Graph Extraction and Prevention
Extracting the deadlock graph from the system_health Extended Events ring buffer, understanding victim election, and architectural resolution.
SQL Server Pool Timeout: Connection Pool Exhaustion Fix
Difference between connection pool timeout and query command timeout, detecting connection leaks, and .NET DbContext architectural fixes.