SQL Server PowerShell : How to List SQL Server Services using PowerShell

Catch: this lists any service that has “SQL” in it, so can potentially list MySQL services too 🙂

ListAll SQL services

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

#list stopped SQL Server services
$computername = “APHRODITE”
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
($_.name -like “SQLAGENT*” -or $_.name -like “*SQL*”) `
}
[/powershell]

Another way to do this:
[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
#note ComputerManagement works on SQL Server 2005
#on SQL Server 2008 this has to be ComputerManagement10
Get-WmiObject `
-namespace rootMicrosoftSqlServerComputerManagement `
-class SqlService | Select-Object ServiceName, DisplayName, SQLServiceType, State
[/powershell]

List Stopped SQL services

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

#list stopped SQL Server services
$computername = “APHRODITE”
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
($_.name -like “SQLAGENT*” -or $_.Name -like “*SQL*”) `
-and $_.State -match “Stopped”
}
[/powershell]

List Running SQL services

[powershell collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

#list stopped SQL Server services
$computername = “APHRODITE”
Get-WmiObject win32_service -computername $computername |
select name, state |
where {
($_.name -like “SQLAGENT*” -or $_.Name -like “*SQL*”) `
-and $_.State -match “Stopped”
}
[/powershell]

4 thoughts on “SQL Server PowerShell : How to List SQL Server Services using PowerShell

  1. Nil November 28, 2012 / 2:43 am

    Running services seems to be a copy and paste from stopped services…
    Thanks for the post.
    By the other hand, good blog.

    Like

  2. enrique February 22, 2013 / 8:24 pm

    How to List SQL Server Services using PowerShell – how can we get the report of SQL Server & Agent services running for Multiple servers.
    I would like to have it into Excel.

    Like

  3. bluegirl April 9, 2013 / 9:25 am

    hi. can i ask for a script if i’d like to only check one server?

    Like

  4. Inez May 26, 2013 / 6:50 pm

    I savour, lead to I found just what I was taking a look for.

    You’ve ended my 4 day lengthy hunt! God Bless you man. Have a nice day. Bye

    Like

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s