Check Windows Services Status with T-SQL

Problem

A common operation that a system administrator does is execute the services.msc command in a Windows environment to see the status of all of the server's services (running, stopped, etc.).

The SQL Server DBA, however, focuses only on the SQL Server services as seen in the sys.dm_server_services dynamic view and does not have an immediate and a full picture from within the SQL Server tools on all services installed on the server.

This tip suggests a T-SQL code solution for this problem.

Solution

My solution involves creating a T-SQL stored procedure in the SQL Server master database, called dbo.usp_Display_Services_By_Status that accepts a single status parameter and lists the windows service names, display name and status filtered by running or stopped services.

Here is the logic of the stored procedure in five simple steps:

1. The procedure creates a table @WINSCCMD to store the sc windows command results.
2. The procedure executes the xp_cmdshell command using this Windows command 'sc queryex type= service state= all'. This command produces text for each service that looks like this.
   1. The first line is the service name, second line is the display name, third line is the service type and the fourth line is the service state (running, stopped, etc.). Other data in the following lines are Windows exit code, service exit code, check point, wait hint, PID and flags.

3. The output of the command in (2) is inserted into the @WINSCCMD table.
4. The query that produces the result is actually a semi-self-join between three copies of the same @WINSCCMD table. The join brings the first, second and fourth data items from each section. In other words, it extracts the service name, display name and state because the structure of each section is fixed.
5. The output result is filtered by the service status (usually running or stopped)

SQL Server Stored Procedure

-- ====================================================================================
-- Author:      Eli Leiba
-- Create date: 06-10-2020
-- Description: display all Windows services according to service status
-- ====================================================================================
CREATE PROCEDURE dbo.usp_Display_Services_By_Status (@stat varchar (20))
AS
BEGIN
 
   DECLARE @WINSCCMD TABLE (ID INT IDENTITY (1,1) PRIMARY KEY NOT NULL, Line VARCHAR(MAX))
 
   INSERT INTO @WINSCCMD(Line) EXEC master.dbo.xp_cmdshell 'sc queryex type= service state= all'
 
   SELECT   trim (SUBSTRING (W1.Line, 15, 100)) AS ServiceName
          , trim (SUBSTRING (W2.Line, 15, 100)) AS DisplayName
          , trim (SUBSTRING (W3.Line, 33, 100)) AS ServiceState
   FROM @WINSCCMD W1, @WINSCCMD W2, @WINSCCMD W3
   WHERE W1.ID = W2.ID - 1 AND
         W3.ID - 3 = W1.ID AND
         TRIM (LOWER (SUBSTRING (W3.Line, 33, 100))) = TRIM(@stat)
END
GO

Sample Execution

Finding all running services:

use master
go
exec usp_Display_Services_By_Status @stat ='running'
go

Finding all stopped services:

use master
go
exec usp_Display_Services_By_Status @stat ='stopped'
go

Notes

The procedure requires that xp_cmdshell is enabled. Not everyone likes to enable xp_cmdshell, so do so based on your needs.

The following script enables xp_cmdshell and should be executed by someone with sysadmin permissions.

use master
go
exec sp_configure 'show advanced options',1
go
reconfigure with override
exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

The procedure was tested using SQL Server 2019.

Next Steps

• You can create and compile this stored procedure in your master database and use it as a simple T-SQL tool for displaying the list of Windows services by state (running or stopped).

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019