By: Eli Leiba | Comments (10) | Related: > TSQL
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:
- The procedure creates a table @WINSCCMD to store the sc windows command results.
- 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.
- 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.
- The output of the command in (2) is inserted into the @WINSCCMD table.
- 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.
- 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).
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips