2

I know I can use SQL Server Configuration Manager to determine 1) Which services are currently running and 2) which services are set to auto start. However, I have several dozen servers I manage. I'd like to run a quarterly check to make sure all services are still set to auto start and to see which services are currently on. Is there a table or procedure that will give me this information?

  • This would be better achieved with a WMI script, e.g. PowerShell, to query each machine's service manager. – dartonw Mar 11 '15 at 18:32
1

I recommend using @Mike's awesome Powershell script. However as an alternative if your SQL Servers are 2008R2 SP1 or latest and if you have list of servers registered under "Local Server Groups" or "Central Management Servers" you can run use the following query to get service status info:

SELECT   servicename ,
        startup_type ,
        startup_type_desc ,
        status ,
        status_desc ,
        process_id ,
        last_startup_time ,
        service_account ,
        filename ,
        is_clustered ,
        cluster_nodename
FROM    sys.dm_server_services

You can also use this to see status of SQL services in an instance with out actually RDPing and opening configuration manager. You can read more about sys.dm_server_services DMV here

  • Thanks, I had to change my accepted answer to this one, because I was really hoping to avoid powershell. I still really need to dig into that functionality more before I feel comfortable using it. – Chris Woods Mar 11 '15 at 23:41
  • Only way to use Powershell is to use Powershell. ;) – Mike Fal Mar 12 '15 at 2:23
  • 1
    Please note that this will not work for all version of SQL Server. This will also not work if SQL Server isn't running, whereas wmi or powershell will still work for all versions and if the SQL Servers aren't started. – Sean says Remove Sara Chipps Mar 12 '15 at 2:52
5

I would use Powershell for this task. Here's a function that leverages Get-WMIObject, WQL, and some syntax to take a list of servers to analyze.

function Get-SQLServiceInfo{
    param([string[]] $servers)
    $return = @()
    $wql = "SELECT * FROM Win32_Service WHERE name like '%SQL%' and (startmode != 'Auto' or state != 'Running')"

    foreach($server in $servers){
        $return += Get-WmiObject -ComputerName $server -query $wql | select name,startmode,state
    }

    $return
}

Get-SQLServiceInfo 'localhost'

Now, there are several ways to approach the matter of scanning all your SQL instances. I like to use Central Management Server for this task. The code I would use looks something like this(with the call to the above function):

$CMS=’CMSSERVER’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms

Get-SQLServiceInfo $cms

So you could run this report manually, or implement it in some additional code to build and email reports out to you. And with it automated, I'd actually say you could have this run once a day instead of quarterly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.