Handling Windows Processes using Windows PowerShell for SQL Server

By: Manvendra Singh   |   Updated: 2013-07-01   |   Comments (4)   |   Related: > PowerShell

Problem

Often when managing SQL Server you need to review and change Windows process information for SQL Server processes.  In this tip I will introduce a few PowerShell cmdlets related to accessing and handling Windows processes and I will explain how to use these Windows PowerShell cmdlets.

Solution

A process, in the simplest terms, is the execution of a program. All Windows processes can be viewed and managed using task manager which can be launched by running the 'taskmgr' cmd. We can run this cmd from either Windows cmd prompt, a PowerShell command prompt or by using Start > Run > and typing taskmgr.

NOTE:-Always run PowerShell prompt using "Run As Administrator" mode for administrative tasks.

Getting a List of Windows processes

Get-Process: We can use this cmdlet to lists all Windows processes running on your system. By default, Get-Process returns a process object that has detailed information about the process and supports methods that let you start and stop the process. You can also use the parameters of Get-Process to get file version information for the program that runs in the process and to get the modules that the process loaded. I will discuss this later in this tip.

--List all windows processes through PowerShell cmdlet.
Get-Process


--List all windows processes of a remote computer MANVENDRA through PowerShell cmdlet.
Get-Process -computername MANVENDRA

Apply Filters to Windows Processes

We can apply filters as well to display a specific process or group of processes. Suppose we want to see all processes whose name begins with "SQL" or a specific process like MSSQLSERVER. We will be using -processname or -Name parameter to display all processes whose process name contains "SQL", both of these parameters are optional.  If you want to get more than one process, then just specify more than one executable process name, separating the names with commas.

--Display All processes whose name contains SQL.
Get-process -processname "*SQL*"


--Display All Processes whose name contains SQL without passing any parameter.
Get-Process *SQL*

--Display a specific process.
Get-process SQLSERvr


--Display more than one process.
Get-process SQLSERVR,winword,notepad

You can see an error for the winword process. It is showing this because the Windows Word process was not running at the time when I executed the cmdlet. If any Word document was open then it would have displayed the details of that process.

--Display all processes whose name starts with 'S'.
Get-Process S*


--Get the process of the current PowerShell session
Get-Process -id $pid

We can also get details about all executable programs like productversion and companyName of that process. We can use another cmdlet "Select-object" to filter the details. We can add both cmdlets with the help of pipes and filter it as shown below.

--Display all data for a process.
Get-Process sqlservr | format-list *


--filter data details of all processes.
Get-Process | Select-Object name,fileversion,productversion,company

 I did not attach the output of all the above commands because the result was too huge to capture in a screenshot. I have applied a filter to display the results for one process (i.e SQL Server) with a few data columns. Here two SQL Server processes are shown because I have two SQL Instances running on my machine.

We can also sort the output of get-process on the basis of its output columns like CPU, VM, id etc.

--List the processes using the most CPU time:
Get-Process | sort CPU


--List the processes sort by process ids.
Get-Process | sort id

Stopping and Starting windows processes

Stop-Process: You can stop any process either by specifying the process ID or by specifying the process name. We will use "STOP-Process" cmdlet to stop a process. Stop-Process cmdlet requires processid of the process which needs to be stopped. But we can also stop a process by its process name by passing the -processname parameter followed by the correct processname.

Suppose we want to stop one of the SQL server processes. First run get-process, to list all processes related to SQL Server so we can get the correct process Id and its process name.  We can then stop it as shown below. If you want to stop all processes belonging to SQL Server then we can also do this with the help of filters.

--Check windows process id of all SQL processes.
Get-Process *SQL*


--Get the windows processid from above cmdlet and Stopping the required process.
Stop-process ID 3276


--Stopping a process by processname.
Stop-process -processname sqlagent

You can see in the above screenshot, that first we get the process id by running get-process and then we stopped it by using its process id.

Start-Process: We can start a program by running "start-process" cmdlet. We can start any program which is in the current directory with this method. You can also launch event viewer with the help of this cmdlet along with notepad, Word etc. We can also launch a new PowerShell window with the run as administrator mode through an existing PowerShell cmd prompt.

--Start a process of notepad
Start-Process notepad


--Start a process of eventviewer.(launching eventviewer)
Start-process eventvwr


--Start a powershell window in administrator mode.
Start-Process powershell -verb runas

Next Steps

• Try to use PowerShell cmdlets in your day to day activities. PowerShell is becoming more popular for SQL Server support on Windows core edition.
• Read more tips on PowerShell

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips