Can I check the services currently running through SSMS?

Question

I want to check which services has started and which has not by running a script/query on SSMS.

Answer 1

You can do this with CLR or PowerShell. From PowerShell you can use things like Get-Process or Get-Service for some of this information. With C# you could look at this answer but I don't know of any ready-made code for SQLCLR. Note that with PowerShell you'll need to run the command in the context of the remote computer, e.g.

Invoke-Command -ComputerName RemoteComputerName { Get-Service }

// or if your version of PS is modern enough:

Get-Service -ComputerName RemoteComputerName

You can also use xp_cmdshell to call things like tasklist, net start, wmic or 3rd-party things like Process Explorer (though I haven't tried to invoke that via command line):

EXEC master..xp_cmdshell 'tasklist';
EXEC master..xp_cmdshell 'net start';
EXEC master..xp_cmdshell 'wmic service get';

The latter has much more valuable information, but the output is absolutely horrible to work with.

Of course this all assumes that you and/or the SQL Server service account has sufficient privileges to call these functions.

(Adopted from my answer to a similar question on SO.)

Answer 2

If you're using SQL Server 2008 R2 or higher, you can use the DMV sys.dm_server_services (note: it only reports back for specific SQL Server services, which may or may not meet your requirements).

Otherwise, you'll need to go with a command-line or PowerShell-based check as Aaron has detailed in his answer.

Answer 3

just adding in Aaron reply , you can also use the xp_cmdshell and run powershell

xp_cmdshell 'powershell.exe -noprofile -command "get-service"' 

or the services are not running :

xp_cmdshell 'powershell.exe -noprofile -command "Get-Service |  where {$_.status -eq ''Stopped''}"'

all SQL Services

xp_cmdshell 'powershell.exe -noprofile -command "Get-Service -name *sql* "'

Using the xp_cmdshell, the context of the get-service under xp_cmdshell will be the SQL Connection, so if you are connected to Server1 the output will be from Server1, connected to Server2 will be from Server2..etc

Or you can change the context in the cmdlet :

xp_cmdshell 'powershell.exe -noprofile -command "Get-Service -computername RemoteComputer -name *sql* "'