Monitoring SQL Server Services

As you know SQL Server runs using a bunch of different services in order for it to operate.  These can be monitored, started and stopped using SQL Server Configuration Manager or Windows Services, but this is not always the most convenient way of checking the services.  Is there any way this can be done within SQL Server Management Studio?

One of the tasks of a DBA is to monitor the availability of all SQL Server instances and services. If you have a large number of instances this monitoring task could take you a fair amount of time.

You can monitor services locally but what if you need to get notification a service has stopped on a particular server.

In my post I’ll introduce you undocumented stored procedure: xp_servicecontrol which allow you to get information about status of a particular SQL Server service installed/not installed on a SQL Server.

Extended stored procedure xp_servicecontrol takes two arguments. The first argument identifies the action. The action can be querystate to query the service status, Start to start SQL service, Stop to stop SQL service. The second argument identifies the Windows service (or SQL Server service) to control.

You can use stored procedure: usp_SQL_Server_Services_Check_Utility for checking SQL Server services status locally but it was more interesting to me monitor services on different instances and get notification if any services have been stopped.

USE [DBA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

———————-    SQL Server Service Check Utility   ——————–

CREATE PROCEDURE [dbo].[usp_SQL_Server_Service_Check_Utility]

AS

BEGIN
—————————————— Inital Setup —————————————————–
CREATE TABLE #RegResult
(
ResultValue NVARCHAR(4)
)

IF OBJECT_ID (N’DBA..ServicesServiceStatus’, N’U’) IS NOT NULL
BEGIN
DROP TABLE DBA..ServicesServiceStatus
END
CREATE TABLE DBA..ServicesServiceStatus   /*Create temp tables*/
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus varchar(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)

DECLARE
@ChkInstanceName nvarchar(128)    /*Stores SQL Instance Name*/
,@ChkSrvName nvarchar(128)     /*Stores Server Name*/
,@TrueSrvName nvarchar(128)     /*Stores where code name needed */
,@SQLSrv NVARCHAR(128)      /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128)    /*Stores physical name*/
,@FTS nvarchar(128)       /*Stores Full Text Search Service name*/
,@RS nvarchar(128)       /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128)     /*Stores SQL Agent Service name*/
,@OLAP nvarchar(128)      /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128)      /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY(‘MachineName’) AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY(‘INSTANCENAME’) AS VARCHAR(128))
SET @ChkInstanceName = @@serverName

IF @ChkSrvName IS NULL        /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = ‘MSQLSERVER’
SELECT @OLAP = ‘MSSQLServerOLAPService’  /*Setting up proper service name*/
SELECT @FTS = ‘MSFTESQL’
SELECT @RS = ‘ReportServer’
SELECT @SQLAgent = ‘SQLSERVERAGENT’
SELECT @SQLSrv = ‘MSSQLSERVER’
END
ELSE
BEGIN
SET @TrueSrvName =  CAST(SERVERPROPERTY(‘INSTANCENAME’) AS VARCHAR(128))
SET @SQLSrv = ‘$’+@ChkSrvName
SELECT @OLAP = ‘MSOLAP’ + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = ‘MSFTESQL’ + @SQLSrv
SELECT @RS = ‘ReportServer’ + @SQLSrv
SELECT @SQLAgent = ‘SQLAgent’ + @SQLSrv
SELECT @SQLSrv = ‘MSSQL’ + @SQLSrv
END
———————————- SQL Server Service Section ———————————————-

SET @REGKEY = ‘System\CurrentControlSet\Services\’+@SQLSrv

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Sever service*/
EXEC xp_servicecontrol N’QUERYSTATE’,@SQLSrv
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘MS SQL Server Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘MS SQL Server Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

———————————- SQL Server Agent Service Section —————————————–

SET @REGKEY = ‘System\CurrentControlSet\Services\’+@SQLAgent

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Agent service*/
EXEC xp_servicecontrol N’QUERYSTATE’,@SQLAgent
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘SQL Server Agent Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘SQL Server Agent Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
———————————- SQL Browser Service Section ———————————————-
DECLARE @servicename  VARCHAR(100)

SET @servicename = @@servicename
PRINT @servicename

IF @servicename <> ‘MSSQLSERVER’
BEGIN

SET @REGKEY = ‘System\CurrentControlSet\Services\SQLBrowser’

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of SQL Browser Service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,N’sqlbrowser’
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘SQL Browser Service – Instance Independent’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘SQL Browser Service – Instance Independent’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

END

———————————- Integration Service Section ———————————————-

SET @REGKEY = ‘System\CurrentControlSet\Services\MsDtsServer’

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Intergration Service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,N’MsDtsServer’
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Intergration Service – Instance Independent’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Intergration Service – Instance Independent’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

———————————- Reporting Service Section ————————————————

SET @REGKEY = ‘System\CurrentControlSet\Services\’+@RS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Reporting service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@RS
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Reporting Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Reporting Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

———————————- Analysis Service Section ————————————————-
IF @ChkSrvName IS NULL        /*Detect default or named instance*/
BEGIN
SET @OLAP = ‘MSSQLServerOLAPService’
END
ELSE
BEGIN
SET @OLAP = ‘MSOLAP’+’$’+@ChkSrvName
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@OLAP
END

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Analysis service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@OLAP
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Analysis Services’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Analysis Services’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

———————————- Full Text Search Service Section —————————————–

SET @REGKEY = ‘System\CurrentControlSet\Services\’+@FTS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT DBA..ServicesServiceStatus (ServiceStatus)  /*Detecting staus of Full Text Search service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@FTS
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Full Text Search Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO DBA..ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE DBA..ServicesServiceStatus set ServiceName = ‘Full Text Search Service’ where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE DBA..ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

————————————————————————————————————-
SELECT   PhysicalSrverName AS ‘Physical Server Name’    /*Display finding*/
,ServerName AS ‘SQL Instance Name’
,ServiceName AS ‘SQL Server Services’
,ServiceStatus AS ‘Current Service Service Status’
,StatusDateTime AS ‘Date/Time Service Status Checked’
FROM DBA..ServicesServiceStatus

————————————————————————————————————-
–DROP TABLE DBA..ServicesServiceStatus    /*Perform cleanup*/
DROP TABLE #RegResult

END

I made one assumption; I’m going to monitor SQL Browser only on named instances.

I created this stored procedure on every server I’m going to monitor in my case in DBA database.

For realization of this goal you need to have one server where you can create SQL Agent job. This server shouldn’t be rebooted often. All Service Packs should be applied on this server. It would be a SQL Server 2008, for instance.

job

In 1-st step you run the stored procedure on remote server you’re going to monitor.

Job_1stStep.PNG

In 2-nd step you check if any service is stopped and send notification:

 

DECLARE @cnt  INT

SELECT @cnt = COUNT(ServiceName)
FROM DBA..ServicesServiceStatus
WHERE ServiceStatus = ‘Stopped.’

IF @cnt>0

–Create script to send notification

You’ll need to create a linked server pointed to the server you’re going to monitor. Unfortunately the login you’re going to use as a security context in Linked Server has to have sysadmin permissions or you’ll get the following error running stored procedure: The execute permission was denied on the object ‘xp_servicecontrol’ . Don’t forget to enable RPC.

LinkedServer.PNG

linksql login has sysadmin permissions on server you monitor.

Schedule this job to execute every 15 min and get notifications if any service is stopped.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s