Copy-DbaDbTableData leaks source connection and SqlDataReader when bulk copy fails

[mangenendt-sudo]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

The error surfaced to the caller is just an example of a transient network failure during WriteToServer - in our case an unstable link to Azure SQL:

An existing connection was forcibly closed by the remote host
(German original: Eine vorhandene Verbindung wurde vom Remotehost geschlossen)

That error itself is not the bug and any failure during WriteToServer will do. The actual problem is the side effect: after any exception, the source connection and the SqlDataReader are never closed.

On the source instance a session remains stuck indefinitely:

session_id  status     command     wait_type          wait_time  total_elapsed_time
141         suspended  SELECT      ASYNC_NETWORK_IO   1779       834926

query_text: SELECT [...] FROM SourceTable

The orphaned reader holds a schema stability lock, which then blocks any DDL on the source table:

session_id  command     wait_type   wait_time  blocking_session_id
51          DROP TABLE  LCK_M_X     444463     141

The only way to recover is KILL 141 by hand.

Steps to Reproduce

Run any Copy-DbaDbTableData call and interrupt the destination connection while WriteToServer is still running, for example by issuing KILL <spid> against the destination session or by dropping the network link.

Then check the source instance:

SELECT session_id, status, command, wait_type
FROM sys.dm_exec_requests
WHERE wait_type = 'ASYNC_NETWORK_IO';

The SELECT from the failed attempt is still there and stays until killed manually.

Please confirm that you are running the most recent version of dbatools

2.8.4

Other details or mentions

Analysis

In public/Copy-DbaDbTableData.ps1 the cleanup of the source side lives inside the try block, right after WriteToServer:

$bulkCopy.WriteToServer($reader)
...
$server.ConnectionContext.SqlConnectionObject.Close()   # source
$bulkCopy.Close()
$bulkCopy.Dispose()
$reader.Close()                                          # reader
...
} catch {
    Stop-Function -Message "Something went wrong" -ErrorRecord $_ -Target $server -continue
} finally {
    if ($bulkCopyConnection) {
        $bulkCopyConnection.Close()      # only the DESTINATION is cleaned up
        $bulkCopyConnection.Dispose()
    }
}

If WriteToServer throws, execution jumps straight to catch, so those four lines never run. The finally block only disposes $bulkCopyConnection (destination). $reader, $bulkCopy and the source connection are leaked.

Suggested fix

Move the source-side cleanup into the existing finally block, mirroring what is already done for the destination:

} finally {
    if ($reader) {
        try { $reader.Close(); $reader.Dispose() } catch { }
    }
    if ($bulkCopy) {
        try { $bulkCopy.Close(); $bulkCopy.Dispose() } catch { }
    }
    if ($server.ConnectionContext.SqlConnectionObject.State -eq 'Open') {
        try { $server.ConnectionContext.SqlConnectionObject.Close() } catch { }
    }
    if ($bulkCopyConnection) {
        $bulkCopyConnection.Close()
        $bulkCopyConnection.Dispose()
    }
}

Why this matters

The function is a natural fit for chunked/retry loops over unreliable links. In such a loop every failed attempt leaks one source session. In our case 106 chunks produced 2 transient failures; each left an ASYNC_NETWORK_IO session behind that eventually blocked DROP TABLE on a persisted staging table, created before the sync, requiring manual intervention.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.9020
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.9020
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Source:
Microsoft SQL Server 2019 (RTM-GDR) (KB5102336) - 15.0.2180.2 (X64) Jun 18 2026 18:09:53 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) (on-premises)

Destination:
Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 19 2026 09:20:28 Copyright (C) 2026 Microsoft Corporation

.NET Framework Version

.NET Framework 4.8.4795.0