Timeout expired obtaining a connection from the pool in PowerShell System.Data.SqlClient scripts

Deterministic Cleanup for SqlConnection

To prevent "Timeout expired" errors in PowerShell, you must implement a try...finally block to explicitly call .Dispose() or .Close() on every SqlConnection object. Because PowerShell does not automatically call Dispose() when a variable goes out of scope, connections remain active in the .NET pool until the Garbage Collector (GC) eventually runs or the session terminates.

The Reliable Implementation Pattern

The most robust pattern for reusable functions is to instantiate the connection and immediately wrap its usage in a try block, ensuring the finally block handles the cleanup regardless of whether the script succeeded or threw an exception.

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
try {
    $connection.Open()
    # Execute your SQL commands here
} 
finally {
    if ($null -ne $connection) {
        $connection.Dispose()
    }
}


Technical Explanation and Assumptions
This approach assumes the use of System.Data.SqlClient (or Microsoft.Data.SqlClient) in PowerShell 5.1 or 7+. In these environments, SqlConnection implements IDisposable. While .Close() and .Dispose() are functionally equivalent for returning a connection to the pool, .Dispose() is the standard .NET practice for resource cleanup.

Why scope-based disposal fails: Unlike C# using statements, PowerShell does not provide a native language construct that guarantees the disposal of .NET objects at the end of a block. If a script opens connections in a loop or a frequently called function without explicit disposal, the pool will reach its Max Pool Size (default 100) long before the GC triggers a collection.

Verification and Diagnostics
To verify that your cleanup pattern is working, you can monitor active connections on the SQL Server instance using Dynamic Management Views (DMVs):
SELECT count(*) as ConnectionCount 
FROM sys.dm_exec_connections 
WHERE client_net_address = 'Your_Client_IP';

If the count increases steadily during script execution and does not drop immediately after the function completes, a leak is still present.

Recommendation Summary

    
Do: Use try...finally to call .Dispose().
    
Do: Ensure cleanup happens in every possible code path (including errors).
    
Avoid: Increasing Max Pool Size in the connection string; this only delays the crash.
    
Avoid: Relying on the Garbage Collector for high-frequency connection cycles.


Diagnostic Question: Are you using a shared global connection object across multiple functions, or is a new connection instantiated within each function call?