Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handling Windows Processes using Windows PowerShell for SQL Server


By:   |   Read Comments (4)   |   Related Tips: More > PowerShell

Attend these FREE SQL Server 2017 webcasts >> click to register


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*

List All processes which contails SQL.

--Display a specific process.
Get-process SQLSERvr

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

List detail about a single process SQL Server.

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

Get process with the help of process id.

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

Get the metadta details of the process.

 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

sort the processes by CPU and IDs.

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

Stop a particular process.

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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, December 10, 2014 - 12:02:39 PM - Tom Back To Top

Very Nice Information.Hopefully you will write on using some powershell quick commands for sqlserver.


Wednesday, July 09, 2014 - 9:40:12 AM - Sushil Back To Top

Thanks ..useful information.

One more addition..

Get-Process | sort CPU|out-gridview


Saturday, July 20, 2013 - 7:54:10 AM - Vikrant S Patil Back To Top

nice and simple explanation, Thanks.


Monday, July 08, 2013 - 9:52:49 PM - Deepak Kumar Back To Top

Very Impressive One!! Thanks for sharing.


Learn more about SQL Server tools