Showing and Killing SQL Server Processes with PowerShell

1 minute read

Another post in the PowerShell Box of Tricks series. There are much better ways of doing this I admit but as you can do it with PowerShell I created a function to do it.

Create a Server Object and notice that there is a Method named EnumProcesses by piping it to Get-Member and then look at the Properties and Methods of EnumProcesses

image

Once I had done that then it was easy to create a function to display what is going on. It’s quick and easy. Not as good as sp_WhoIsActive but it displays about the same info as sp_who, sp_who2

image

image

You can also find a Method called KillProcess on the Server Property so I asked a Yes/No question using Windows Forms. You can find much more detail on that here

image

All you need to supply is the spid

#######################################################################
#
# NAME: Show-SQLProcesses.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:06/08/2013
#
# COMMENTS: Load function for Showing Processes on a SQL Server
####################################

Function Show-SQLProcesses ($SQLServer)

{
$server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
$Server.EnumProcesses()|Select Spid,BlockingSpid, Login, Host,Status,Program,    Command,Database,Cpu,MemUsage |Format-Table -wrap -auto

$OUTPUT= [System.Windows.Forms.MessageBox]::Show("Do you want to Kill a     process?" , "Question" , 4) 

if ($OUTPUT -eq "YES" ) 
{

$spid = Read-Host "Which SPID?"
$Server.KillProcess($Spid)


} 
else 
{ 

}

}

Comments