When This Error Shows Up
You're running a big SQL import — maybe from a CSV, an SSIS package, or a linked server query. The import chugs along for about 30 minutes, then dies. You get error 10054 (TCP Provider: An existing connection was forcibly closed by the remote host) or 10053. Sometimes it just says "Connection lost" or "Query timeout expired". This is not random. It happens at the same point in the import every time.
Root Cause — Plain English
The culprit here is almost always the remote query timeout setting in SQL Server. By default, it's 600 seconds — exactly 10 minutes. But the actual drop at 30 minutes? That's often a network device (firewall, load balancer) or SQL Server's remote query timeout set to 1800 seconds. Another common one: the client driver's Connection Timeout or Command Timeout defaults. Don't bother checking disk space or memory — those errors look different. This is a timeout, plain and simple.
Fix — Step by Step
Step 1: Check the SQL Server remote query timeout
Run this query on your SQL Server instance:
sp_configure 'remote query timeout';If it returns anything under 0 (0 means no timeout), set it to 0:
sp_configure 'remote query timeout', 0;
RECONFIGURE;I've seen this set to 600 or 1800 seconds on many production boxes. That's your 10- or 30-minute cutoff.
Step 2: Increase the client-side Command Timeout
If you're using SQL Server Management Studio (SSMS), go to Tools > Options > Query Execution > SQL Server. Set Execution time-out to 0 (unlimited). Also check Remote Query Timeout in the same section.
For SSIS, change the CommandTimeout property on your Execute SQL Task or OLE DB Source. Default is 0, but some templates set it to 30 seconds. Set it to 0.
Step 3: Check your network gear
Firewalls and load balancers often have idle connection timeouts. 30 minutes is a common default (Cisco ASA, Fortinet, AWS Network Load Balancer). Ask your network team to set idle timeout to at least 1 hour for the SQL Server port (1433). Or you can keep the connection alive by sending small "keep-alive" queries every 20 minutes in your import script.
Step 4: Increase connection timeout at the driver level
In your connection string, add Connection Timeout=300 (5 minutes) or 0 for unlimited. For .NET apps:
Server=myServer;Database=myDB;Integrated Security=true;Connection Timeout=0;Command Timeout=0;For ODBC or OLEDB, set Connect Timeout=0 and Query Timeout=0.
Step 5: Check the SQL Server log for clues
Look at the SQL Server error log (xp_readerrorlog) or Windows Application log around the time of the drop. Search for "timeout" or "10054". If the log says "A connection was successfully established with the server, but then an error occurred during the login process", that's a different issue — check your credentials and encryption settings.
What to Check If It Still Fails
If none of that fixed it, here's what I've seen cause weird drops:
- Network packet size — set it to 4096 or 8192 in your connection string:
Packet Size=4096. Some gear drops large packets. - SQL Server itself is restarting — check SQL Server Agent jobs or Windows updates. A restart kills all connections.
- Linked server timeout — if you're using a linked server in your import, check the linked server's
query timeoutproperty. Right-click the linked server in SSMS > Properties > Server Options. Set Query Timeout to 0. - Corrupt data halfway through — run
DBCC CHECKTABLEon the target table. A corrupt row can cause a connection drop. Unlikely but I've seen it twice in 14 years.
That's it. Nine times out of ten, it's a timeout setting. Don't overthink it.