I was looking for a way to grab a list of processes running inside Sql Server but wasn’t having much luck. Essentially I wanted something like the Get-Process cmdlet but for Sql Server. Shortly after tweeting for help I stumbled across the EnumProcesses SMO method.

Using this is quite simple. To list all Sql Server processes;

# List all sql server processes
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sql_server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlinstance";
$processes = $sql_server.EnumProcesses();
$processes | Format-Table -AutoSize;

To exclude system processes we do;

# Exclude system processes
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sql_server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlinstance";
$processes = $sql_server.EnumProcesses($true);
$processes | Format-Table -AutoSize;

I’m starting to think if you say you can’t do it in Powershell then you haven’t looked hard enough!