Monday, September 23, 2024

What SQL Server services are running on that server?

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 
 ('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 
 WHERE ServiceName IN ('SQL Browser Service - Instance Independent','SSIS')
 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 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;


Hope to have helped!

Wednesday, September 18, 2024

Where is the 1205 error for the SQL Server Deadlock ?!!

Just a short post, but I believe still helpful.  Yesterday I was playing around with different methods of capturing deadlock notifications.  I used XE's (Extended Event Sessions), Service Broker Queue / Event Notifications and SQL Server Alerts. While testing the Alerts, I simulated my own deadlock and produced the deadlock event myself, like this:



The expectation here was that the SQL Server Alert would send me a notification for the deadlock event... but it didn't.  Curious.  I verified the mail and the operator, I verified the last_occurrence_date of the alert... everything checked out good, but there still wasn't any 1205 recorded in the SQL Server Error log.  Then I just decided to be sure the 1205 was in sys.messages:







We can see it IS there, but do you see that "is_event_logged" = 0 ?  This means it is not going to be recorded in the SQL Server Error log.  Easily amended:

               EXEC master.sys.sp_altermessage
                  @message_id = 1205,
                  @parameter = 'WITH_LOG',
                  @parameter_value = 'true';

I generated the next deadlock and the 1205 is now in my Error Log:



All is well now, and I can move forward w/the deadlock capture.  I will post that here when it is complete.  See you soon!