Check Windows Services Status with T-SQL


By:   |   Updated: 2020-11-05   |   Comments (9)   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


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.
output from sc queryex
  1. The output of the command in (2) is inserted into the @WINSCCMD table.
  2. 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.
  3. 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
stored procedure results

Finding all stopped services:

use master
go
exec usp_Display_Services_By_Status @stat ='stopped'
go
stored procedure results

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).


Last Updated: 2020-11-05


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips



Comments For This Article




Monday, December 14, 2020 - 1:36:41 PM - Salam Back To Top (87913)
JSON idea is good, couple of questions, in SELECT * from openjson(@JSON) .....how I can sort by $.status? 2nd if using json to be in a SP, how status can be passes as string or int? Thanks

Monday, December 14, 2020 - 12:45:01 PM - Eli Leiba Back To Top (87912)
Thanks for all the compliments and the other ideas presented here.

Sunday, December 13, 2020 - 1:21:31 PM - Salam Back To Top (87906)
Yes, cool SP qnd useful, also the idea just to limit for specific services is great, well done

Thursday, November 26, 2020 - 3:22:47 AM - Joost Back To Top (87853)
I found out that on older SQL versions (I tested my script on SQL 2019), the order in the @JSON_TBL can be wrong, so you need to add an identity column and order by that identity column:

DECLARE @JSON_TBL TABLE(id INT IDENTITY(1,1),row NVARCHAR(MAX))
DECLARE @JSON NVARCHAR(MAX)
INSERT @JSON_TBL(row) EXEC xp_cmdshell 'powershell.exe -command "get-service | select Name, @{l="""Status""";e={$_.Status.ToString()}} | ConvertTo-Json"'
select @JSON = COALESCE(@JSON,'') + LTRIM(row) from @JSON_TBL where row IS NOT NULL order by id
SELECT @JSON
select * from @JSON_TBL

SELECT * from openjson(@JSON)
WITH (
Name NVARCHAR(100) '$.Name',
Status NVARCHAR(100) '$.Status'
)

Tuesday, November 24, 2020 - 9:01:04 AM - Joost Back To Top (87832)
Another possibility is to convert the powershell output to JSON and parse that. I'm sure that this can be done more elegantly, but this works. If you convert the default output of Get-Service to JSON, the status field is an int, @{l="""status""";e={$_.status.ToString()}} turns this into a string.

DECLARE @JSON_TBL TABLE(row NVARCHAR(MAX))
DECLARE @JSON NVARCHAR(MAX)
INSERT INTO @JSON_TBL EXEC xp_cmdshell 'powershell.exe -command "get-service | select name, @{l="""status""";e={$_.status.ToString()}} | convertto-Json"'
select @JSON = COALESCE(@JSON,'') + row from @JSON_TBL WHERE ROW IS NOT NULL

SELECT * from openjson(@JSON)
WITH (
Name NVARCHAR(100) '$.Name',
Status NVARCHAR(100) '$.status'
)

Tuesday, November 24, 2020 - 7:34:01 AM - TOm Back To Top (87831)
Cool! Thank you!

Tuesday, November 24, 2020 - 4:49:40 AM - Joost Back To Top (87829)
EXEC xp_cmdshell 'powershell.exe -command "get-service | select @{l="""servicestatus""";e={"""$($_.name);$($_.status)"""}} | select -expandproperty servicestatus"'

And then split again on ; to get service name and service status

Thursday, November 05, 2020 - 9:45:42 PM - Pds Back To Top (87763)
Very good article and useful to know the service status but when server is down so services down so how can I know the notifications?
Sql service down then you canít run the stored procedure

Thursday, November 05, 2020 - 10:26:00 AM - Bill Bergen Back To Top (87761)
Hi Eli
First let me say that all your work and scripts are both brilliant and extremely helpful
I would like to submit the following query that I did not turn into a stored procedure for use in sql server 2016 where the TRIM verb does not exist yet. Also, it has been written to allow any character in the status to be checked and will show only sql server services
Pleas let me know your thoughts at your convenience

NOTE THE FOLLOWING IS AN ADAPTATION BY BILL BERGEN ON THURSDAY, 11/05/2020. THIS VERSION HAS BEEN TESTED ON SQL SERVER 2016 WHERE THE TRIM VERB HAS NOT BEEN IMPLEMENTED YET. IT IS OBVIOUSLY NOT SET UP AS A STORED PROCEDURE AND WILL SHOW ONLY AND ALL SQL SERVER SERVICES

---
---
---
DROP TABLE IF EXISTS #WINSCCMD
---
---
---
DECLARE @status_to_be_checked varchar (20);
SET @status_to_be_checked = '%' --- WILL SHOW ALL STATUSES
SET @status_to_be_checked = 'R' --- WILL SHOW ALL STATUS THAT ARE RUNNING (BEGIN WITH R%)
SET @status_to_be_checked = 'S' --- WILL SHOW ALL STATUS THAT ARE STOPPED (BEGIN WITH S%)


---
---
---
CREATE TABLE #WINSCCMD
(
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
RTRIM(LTRIM (SUBSTRING (W1.Line, 15, 100))) AS ServiceName
, RTRIM(LTRIM (SUBSTRING (W2.Line, 15, 100))) AS DisplayName
, RTRIM(LTRIM (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
RTRIM(LTRIM (LOWER (SUBSTRING (W3.Line, 33, 100)))) like RTRIM(LTRIM(@status_to_be_checked)) + '%' ----FOR PRE SQL SERVER 2017
AND
(
RTRIM(LTRIM (SUBSTRING (W1.Line, 15, 100))) LIKE '%SQL%'
OR
RTRIM(LTRIM (SUBSTRING (W2.Line, 15, 100))) LIKE '%SQL%'
)
---
---
---
DROP TABLE IF EXISTS #WINSCCMD




download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms