I want to check which services has started and which has not by running a script/query on SSMS.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You can do this with CLR or PowerShell. From PowerShell you can use things like
You can also use xp_cmdshell to call things like
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.)
If you're using SQL Server 2008 R2 or higher, you can use the DMV
Otherwise, you'll need to go with a command-line or PowerShell-based check as Aaron has detailed in his answer.
just adding in Aaron reply , you can also use the xp_cmdshell and run powershell
or the services are not running :
all SQL Services
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 :