How to Check and Manage SQL Server Services

As a SQL Server DBA, it’s important that we have quick and efficient ways to check SQL Services. We need to ensure our SQL Services are always available, and we often need to restart or make changes to them in SQL Server Configuration Manager. This includes the SQL Server Engine, Agent, and any other service you are relying on.

There are many ways to check running SQL Services, including via PowerShell, SQL Server Configuration Manager, or by querying SQL DMVs. In this blog post, I’m showing how to check SQL Services on Windows, which includes the following:
1. SQL Query to Check Services Information
2. Checking Services in SQL Server Configuration Manager
3. Other Methods to Check SQL Server Services

1. SQL Query to Check Services Information

Using a SQL query to check services information is the go to choice for a Database Admin. We need to have good eyes on this information, and if any of my critical SQL Servers were to go down, we need an alert.

Run the following query to show a snapshot of SQL Server services, including the SQL Agent and key information:

-- SQL Server Services Info
SELECT servicename, process_id, startup_type_desc, status_desc, 
last_startup_time, service_account, is_clustered, cluster_nodename, [filename], 
instant_file_initialization_enabled -- New in SQL Server 2016 SP1
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

We’re querying the sys.dm_server_services DMV, which returns the following (and more):
> servicename : The name of the SQL Server service.
> process_id : The process ID for the service.
> startup_type_desc : The startup type (e.g., Automatic, Manual).
> status_desc : Current service status (e.g., Running, Stopped).
> last_startup_time : Timestamp of the last service startup.
> service_account : The account the service is running under.
> is_clustered : Whether the service is clustered.
> cluster_nodename : Name of the cluster node (if clustered).
> filename : Path of the service executable.
> instant_file_initialization_enabled : Whether instant file initialization is enabled (available from SQL Server 2016 SP1).

2. Checking Services in SQL Server Configuration Manager

SQL Server Configuration Manager is the best place and practice for managing and checking SQL Server services. It is the tool we should use for starting and stopping (and configuring) services, as apposed to doing via Services.msc.

If you’re working in a high-availability environment with Always On Availability Groups (AGs), you’ll often be managing services through Failover Cluster Manager to ensure proper failover and service health.

SQL Server Configuration Manager should be installed on your machine if it has SQL Server installed. If you can’t find it by searching for the app, it can be found in the following locations:

SSMS 18
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\Application
SSMS 19
C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\Extensions\Application
SSMS 20
C:\Program Files (x86)\Microsoft SQL Server Management Studio 20\Common7\IDE\Extensions\Application

You can restart and manage your MSSQL services within here.

3. Other Methods to Check SQL Server Services

Aside from using the SQL query and SQL Server Configuration Manager, there are some other ways we can view and manage SQL Server services, including:

PowerShell
We can use Get-Service, Start-Service, Stop-Service and Restart-Service cmdlets.

Services.msc
As mentioned within this post we can view and restart SQL Server services within Windows Services, but it’s not the recommended way (it’ll be fine, don’t worry).

Hope all this was useful and what you were looking for. Feel free to checkout other links around for more random tips from a SQL Server DBA!