Stop-DbaExternalProcess

Synopsis

Terminates operating system processes spawned by SQL Server instances

Description

Terminates external processes that were created by SQL Server, such as those spawned by xp_cmdshell, BCP operations, SSIS packages, or external script executions. This function is designed to work with the output from Get-DbaExternalProcess to resolve specific performance issues.

The primary use case is troubleshooting hung SQL Server sessions that display External Wait Types like WAITFOR_RESULTS or EXTERNAL_SCRIPT_NETWORK_IO. When SQL Server is waiting for an external process to complete and that process becomes unresponsive, this command provides a safe way to terminate the problematic process without affecting the SQL Server service itself.

This approach is much more targeted than killing SQL Server sessions directly, as it addresses the root cause (the stuck external process) rather than just terminating the database connection that’s waiting for it.

https://web.archive.org/web/20201027122300/http://vickyharp.com/2013/12/killing-sessions-with-external-wait-types/

Syntax

Stop-DbaExternalProcess
    [-ComputerName] <DbaInstanceParameter>
    [[-Credential] <PSCredential>]
    [[-ProcessId] <Int32>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaExternalProcess -ComputerName SQL01 | Stop-DbaExternalProcess

Kills all OS processes created by SQL Server on SQL01

Example: 2

PS C:\> Get-DbaExternalProcess -ComputerName SQL01 | Where-Object Name -eq "cmd.exe" | Stop-DbaExternalProcess

Kills all cmd.exe processes created by SQL Server on SQL01

Required Parameters

-ComputerName

Specifies the Windows server hosting the SQL Server instance where external processes need to be terminated.
Use this when troubleshooting hung sessions with external wait types on remote SQL Server hosts.

Optional Parameters

-Credential

Allows you to login to $ComputerName using alternative credentials.

-ProcessId

Specifies the Windows process ID of the external process spawned by SQL Server that needs to be terminated.
Typically obtained from Get-DbaExternalProcess output when identifying processes causing EXTERNAL_SCRIPT_NETWORK_IO or WAITFOR_RESULTS wait types.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.