Use PowerShell to Identify the Process ID for SQL Server Services

I recently saw a blog article on How to Identify Process ID for SQL Server Services? – Interview Question of the Week #185 written by Pinal Dave. While his answer is simple with TSQL, what if you're not a SQL guy? You can also retrieve this information with PowerShell from Windows itself.

When it comes to retrieving information about Windows services with PowerShell, the first command that comes to mind is Get-Service. Unfortunately, Get-Service doesn't return the process id for services. This means you'll need to resort to using the CIM cmdlets to retrieve this information.

This information can be retrieved from numerous remote SQL servers with the following command.

1Get-CimInstance -ComputerName sql14, sql16, sql17 -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" |
2Format-Table -Property SystemName, Name, Caption, Status, ProcessId, StartName

One thing that I didn't retrieve with the previous command was the start time of the services which is a little more complicated. In a previous blog article, I wrote about how to Determine the Start Time of a Windows Service with PowerShell. I'll add that functionality in this example.

1Invoke-Command -ComputerName sql14, sql16, sql17 {
2    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" -PipelineVariable Service |
3    Select-Object -Property SystemName, Name, Caption, Status, ProcessId, StartName,
4                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
5} | Format-Table -Property SystemName, Name, Caption, Status, ProcessId, StartTime, StartName

If you wanted to maximize the efficiency of the commands shown in this blog article, you could also specify the Property parameter with Get-CimInstance to only return specific properties instead of all of them.

µ