There are many ways to check the sql services status . we will start with simple command in PowerShell ,ask PowerShell to tell you all of the commands related to services by using below command:
Get-Command -Noun Service
From the above screen shot look at the commands which we use to check the sql services status, start, stop and restart :
Get-Service ServiceName – this is to check the sql services status like Running\Stopped
Example:
Get-Service ‘MSSQL$*’ – This command gives all the Named Instances Installed on your Windows Box
Get-Service ‘MSSQL*’ – This command gives both Named and default Instances Installed on your Windows Box.
start-Service ServiceName – This is to start your specified service
Example :
start-Service MSSQLSERVER – command for Default instance start
start-Service MSSQL$SQL1 – Command for Named Instance start
stop-Service ServiceName – This is to stop your specified service
Example :
stop-Service MSSQLSERVER – command for Default instance stop
stop-Service MSSQL$SQL1 – Command for Named Instance stop
Restart-Service ServiceName – This is to restart your specified service
Example :
Restart-Service MSSQLSERVER – command for Default instance Restart
Restart-Service MSSQL$SQL1 – Command for Named Instance Restart
All the above commands will not work as we have dependent Agent service for Database Engine. So, we need to use -Force parameter in command to force restart as shown below :
restart-service -force MSSQLSERVER – for default Database engine restart
restart-service -force SQLSERVERAGENT – for default Agent engine restart
restart-service -force ‘MSSQL$SQL1’ -for Named Database engine restart
restart-service -force ‘SQLAgent$SQL1’ – for Named Agent engine restart
We can also use below PowerShell script to check the sql services status :
By using below PowerShell Command you can find all SQL related services on our Windows Machine :
Get-Service *sql* |select-object ServiceName,status|Format-Table -AutoSize
OR
By using below PowerShell Command you can find all SQL related services on Node1 Server
Example :
Get-Service -ComputerName NODE1 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status
OR
By using below PowerShell Command you can find all SQL related services on multiple servers[Separate each server name by comma]
Get-Service -ComputerName NODE1,NODE2 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status
Hope this post helps you a lot, I’ve tested above scripts by executing it on SQL Services(both default and Named instance) from my end and works good . Please do some testing before executing it in production environment.
