SQL Server Connection Pool Exhaustion in Linx
If you are getting this error on a Linx solution using the SQL Server connection type:
Timeout expired. The timeout period elapsed prior to obtaining a connection from
the pool. This may have occurred because all pooled connections were in use and
max pool size was reached.
This is connection pool exhaustion. Here is what is happening and how to fix it.
Why This Happens
Linx uses ADO.NET to manage SQL Server connections. ADO.NET maintains a connection
pool per process, per connection string. The default max pool size is 100
connections. When all 100 are in use and a new request comes in, ADO.NET waits.
If no connection frees up within the connection timeout period, you get the error
above.
The trigger is usually Row by row return mode in ExecuteSQL. The connection
stays open for the entire duration of the ForEachRow loop. If your loop does
anything slow (API calls, file writes, nested DB calls), that connection is held
the whole time. Under concurrent load, the pool fills up fast.
This is worth understanding clearly: closing an ADO.NET connection does not destroy
it. It returns the connection to the pool for reuse. The pool only grows when no
existing connection is available to reuse. Exhaustion happens when connections are
held open faster than they are returned.
How to Confirm It
Run this on your SQL Server to see what is sitting there:
SELECT
s.session_id,
s.login_name,
s.status,
s.last_request_end_time,
DATEDIFF(MINUTE, s.last_request_end_time, GETDATE()) AS idle_minutes
FROM sys.dm_exec_sessions s
WHERE s.database_id = DB_ID('YourDatabaseName')
AND s.is_user_process = 1
ORDER BY idle_minutes DESC;
A large number of sleeping sessions with high idle_minutes confirms pool
connections are being held and not returned. If the count is sitting close to
100, you have found your problem.
You can also check the pool ceiling has not already been hit by looking at active
connections against your login specifically:
SELECT
login_name,
COUNT(*) AS connection_count,
status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName')
AND is_user_process = 1
GROUP BY login_name, status
ORDER BY connection_count DESC;
Fixes
1. Raise the pool size as a stopgap
Add Max Pool Size=200 to your connection string. This gives you breathing room
while you address the root cause. It is not a fix on its own. If your solution
is leaking connections, a larger pool just takes longer to exhaust.
Server=yourserver;Database=yourdb;User Id=youruser;Password=yourpassword;Max Pool Size=200;
You can also add a connection timeout so threads fail fast rather than hanging
while waiting for a free connection:
Connection Timeout=15;
2. Switch to “List of rows” where the dataset is small
When ExecuteSQL is set to List of rows, Linx fetches all data into memory
first and releases the connection immediately. The loop processing then happens
against the in-memory list, not against an open connection.
This works well for small, controlled datasets. It carries real risks for large
ones.
Risks to consider before using List of rows:
- Every row is loaded into memory before processing starts. On large result sets,
this can spike memory usage on the Linx Server significantly. - If the query returns tens of thousands of rows, or rows with large data columns,
you risk out-of-memory errors. - If multiple service threads run simultaneously and each loads a large result set,
the memory impact multiplies.
Use List of rows when you know the result set is bounded and small. If the
query can return an unpredictable or large number of rows, keep Row by row and
address the connection lifetime through the other fixes below.
3. Wrap related queries in a BeginTransaction
BeginTransaction opens one connection and holds it for the scope of the
transaction. All ExecuteSQL components inside that scope set to Use transaction
share that single connection rather than each opening their own.
This reduces the number of concurrent connections your solution holds and gives
you explicit control over when the connection opens and closes. It is the most
structurally correct fix for solutions with multiple ExecuteSQL components firing
in sequence.
Keep transactions short. A long-running transaction holds locks on SQL
Server, which creates a different class of problem. Open, do the work, commit.
4. Check for pool fragmentation
ADO.NET creates a separate pool for each unique connection string. Each pool has
its own limit of 100. If your ExecuteSQL components use slightly different
connection strings (different casing, extra spaces, or one component includes
Application Name and another does not), each variation gets its own pool.
This means you could be exhausting multiple pools simultaneously without realising
it. The fix is to standardise the connection string across every ExecuteSQL
component in your solution. Copy one and paste it everywhere. Do not type it by
hand each time.
5. Look at your ForEachRow logic
If you must use Row by row, look at what is happening inside the loop. Any
operation that adds latency extends the time the connection is held open.
- Nested ExecuteSQL calls inside a loop each open their own connection. Under
load, a loop of 500 rows with a DB call per row can hold multiple connections
open simultaneously. - API calls or file operations inside the loop hold the outer connection open for
the duration of each call.
Where possible, restructure to fetch data first, then process. That is the pattern
that keeps connection lifetime short.
The Short Version
Row-by-row processing holds connections open longer than you think, and concurrent
service threads compound it fast. Raise the pool size to stop the bleeding.
Standardise your connection strings to avoid fragmentation. Then restructure your
ExecuteSQL components to release connections sooner, using BeginTransaction for
sequences of queries and List of rows only where the dataset is known to be small.