Good question. 😏 Of course, there are many ways to answer this question in Windows gui-land. A couple examples are the SQL Server Configuration Manager or services.msc, but both are more timely, and it's easy to miss something. This script provides a faster way to gather all SQL Server service details from your server with just one call.
This is the output from one of my v2019 instances:
Here's the script...
/* query status details for all existing SQL Server services */
SET NOCOUNT ON;
-- temp tables
IF (OBJECT_ID ('tempdb..#RegResult')) IS NOT NULL
DROP TABLE #RegResult;
CREATE TABLE #RegResult (
ResultValue
NVARCHAR(4)
);
IF (OBJECT_ID ('tempdb..#ServicesServiceStatus')) IS NOT NULL
DROP TABLE #ServicesServiceStatus;
CREATE TABLE #ServicesServiceStatus(
ID
INT IDENTITY(1,1),
SQLServerName NVARCHAR(128),
ServiceName NVARCHAR(128),
ServiceStatus VARCHAR(128),
CheckDateTime DATETIME DEFAULT (GETDATE()),
PhysicalSrvName NVARCHAR(128)
);
IF (OBJECT_ID ('tempdb..#Services')) IS NOT NULL
DROP TABLE #Services;
CREATE TABLE #Services(
ID INT IDENTITY(1,1),
ServiceName NVARCHAR(128),
DefaultInstance NVARCHAR(128),
NamedInstance NVARCHAR(128)
);
-- load service details
INSERT #Services
VALUES
SET NOCOUNT ON;
IF (OBJECT_ID ('tempdb..#RegResult')) IS NOT NULL
);
SQLServerName NVARCHAR(128),
ServiceName NVARCHAR(128),
ServiceStatus VARCHAR(128),
CheckDateTime DATETIME DEFAULT (GETDATE()),
PhysicalSrvName NVARCHAR(128)
);
ID INT IDENTITY(1,1),
ServiceName NVARCHAR(128),
DefaultInstance NVARCHAR(128),
NamedInstance NVARCHAR(128)
);
INSERT #Services
('MS SQL Server Service','MSSQLSERVER','MSSQL'),
('SQL Server Agent Service','SQLSERVERAGENT','SQLAgent'),
('Analysis Services','MSSQLServerOLAPService','MSOLAP'),
('Full Text Search Service','MSFTESQL','MSSQLFDLauncher'),
('Reporting Service','ReportServer','ReportServer'),
('SQL Browser Service - Instance Independent','SQLBrowser','SQLBrowser'),
('SSIS','MsDtsServer110','MsDtsServer110');
-- declarations
DECLARE
@ChkInstanceName NVARCHAR(128),
@ChkSrvName NVARCHAR(128),
@REGKEY NVARCHAR(128),
@i INT=1,
@Service NVARCHAR(128);
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
-- SQL Server Services selection
WHILE (@i<=(SELECT MAX(ID) FROM #Services))
BEGIN
IF (@ChkSrvName IS NULL OR (
SELECT COUNT(*) FROM #Services
('SQL Server Agent Service','SQLSERVERAGENT','SQLAgent'),
('Analysis Services','MSSQLServerOLAPService','MSOLAP'),
('Full Text Search Service','MSFTESQL','MSSQLFDLauncher'),
('Reporting Service','ReportServer','ReportServer'),
('SQL Browser Service - Instance Independent','SQLBrowser','SQLBrowser'),
('SSIS','MsDtsServer110','MsDtsServer110');
DECLARE
@ChkInstanceName NVARCHAR(128),
@ChkSrvName NVARCHAR(128),
@REGKEY NVARCHAR(128),
@i INT=1,
@Service NVARCHAR(128);
WHILE (@i<=(SELECT MAX(ID) FROM #Services))
IF (@ChkSrvName IS NULL OR (
WHERE ServiceName
IN ('SQL Browser Service - Instance
Independent','SSIS')
AND ID = @i) > 0
AND ID = @i) > 0
)
SELECT @Service= DefaultInstance FROM #Services WHERE ID = @i
ELSE
SELECT @Service= NamedInstance+'$'+CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
FROM #Services WHERE ID = @i
SET @REGKEY = 'System\CurrentControlSet\Services\' + @Service
INSERT #RegResult ( ResultValue
)
EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
-- check service status
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC xp_servicecontrol N'QUERYSTATE',@Service
END
ELSE
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
END
UPDATE #ServicesServiceStatus
SET
ServiceName = (SELECT ServiceName FROM #Services WHERE ID = @i),
SQLServerName = @@SERVERNAME,
PhysicalSrvName = (
SELECT @Service= DefaultInstance FROM #Services WHERE ID = @i
ELSE
SELECT @Service= NamedInstance+'$'+CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
FROM #Services WHERE ID = @i
IF (SELECT ResultValue FROM #RegResult) = 1
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC xp_servicecontrol N'QUERYSTATE',@Service
END
ELSE
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
ServiceName = (SELECT ServiceName FROM #Services WHERE ID = @i),
SQLServerName = @@SERVERNAME,
PhysicalSrvName = (
SELECT CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS VARCHAR(128)))
WHERE
ID = @@IDENTITY
TRUNCATE TABLE #RegResult
SET @i = @i + 1;
END
-- return all details
SELECT * FROM #ServicesServiceStatus
SET NOCOUNT OFF;
WHERE
ID = @@IDENTITY
SET @i = @i + 1;
SELECT * FROM #ServicesServiceStatus
Hope to have helped!