Services and Sorts in PowerShell

Two weeks after my first steps in PowerShell, I got the time to have another play, and have (I think) answered some of the questions I asked myself in that earlier article.

As you recall (or have just read), I was playing around with a PS script to stop all SQL Server Services, and had worked out the following:

Get-Service |
         Where-Object {$_.displayname -like "*SQL*" -and $_.status -eq "Running"} |

Given the way that the services are on this laptop, though, there are issues with that command:

Stop_Service Script errors

In other words, it’s trying to stop services that have dependent services still running. Now, there’s a couple of ways we can deal with this:

Service Dependencies

There are various posts out there talking about Service Dependencies in PowerShell, and I really don’t feel qualified to add anything (or, indeed, to tackle the problem head-on at the moment… I’m blogging / learning in real time here!)

To identify the dependencies of a single service, though, is relatively straightforward:

Get-Service "SQL Server (SQL2012Dev)" -dependentservices

That little snippet returns a collection of services that depend upon the SQL Server (SQL2012Dev) service. Nice, short list – it’s the SQLAgent service for that instance.

So, in theory, we should be able to manipulate those lists of dependent services into an appropriate order for shutting down the remaining services. That’s a bit of a scary step, so I’ll shelve that approach for now.


In my previous blogpost on PowerShell, I asked the question if it would be possible to use the Sort-Object to write a more complex sort operator. And, indeed, it is.

My requirement was for a Sort-Object Sort Order along the lines of the following T-SQL-esque snippet:


And here, in PowerShell, is just that:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($ -like "MSSQL$*") {1} else {0}}}, displayname

So now we have a script that shuts down the services in an order that won’t cause dependency issues:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($ -like "MSSQL$*") {1} else {0}}}, displayname |

Stop_Service Script No Errors

No errors – only warnings, about slow-responding services.


And, in order to restart all the services, without getting errors, try changing the “0” to a “2” in the Sort-Object, and replace “Stop-Service” with “Start-Service”, thus:

Get-Service |
    Where-Object {$_.displayname -like "*SQL*"} |
    Sort-Object -property @{expression={if ($ -like "MSSQL$*") {1} else {2}}}, displayname |

And that all looks good…

Start-Service Script starting


Start-Service Script errors


Turns out that those are error messages from me trying to start services which are marked as “disabled”.

Identifying Disabled Services in PowerShell

At first thought, PowerShell’s Get-Service results object *should* provide a property to indicate the Startup Type of a service. Unfortunately, it doesn’t. There’s a MS Connect item to upvote if you want them to change this, but don’t hold your breath!

There are blogposts out there discussing how to do this using the WMI PowerShell objects, which is something to think about later.

This entry was posted in SQLServerPedia Syndication and tagged . Bookmark the permalink.

2 Responses to Services and Sorts in PowerShell

  1. Anthony Brown says:

    There is a parameter -force that you can pass to stop-service which can be used to stop a service even if it has dependents running. SQL server seems to be clever enough if you do this to stop the agent service before stopping the main sql service.
    So, if I want to stop an instance of sql called sql2008 and sql agent if its running, I just use

    get-service ‘mssql$sql2008’ |stop-service -force

    Note – this works for sql server and sql agent. I do not know if the same thing works for other services with dependent services

  2. thomasrushton says:

    Looking back, it’s a bit simpler than I thought…

    gsv *SQL* | Sort-Object dependencies -descending | Stop-Service

    Seems to be smart enough to do the job.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s