Skip to main content

SQL Server Services

Here is a script to check the status of SQL Server Services and there status from the SSMS


/*---------------------------------------*/
/*    SQL Server Service Check Utility   */
/*---------------------------------------*/

SET NOCOUNT ON

/* ------------------------------------------ Inital Setup -----------------------------------------------------*/
CREATE TABLE #RegResult
(
    ResultValue NVARCHAR(4)
)

CREATE TABLE #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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of SQL Sever service*/
    EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
    UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of SQL Agent service*/
    EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity   
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END


/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/

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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of SQL Browser Service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of Intergration Service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'
    UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of Reporting service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS
    UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of Analysis service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
    UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #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 #ServicesServiceStatus (ServiceStatus)        /*Detecting staus of Full Text Search service*/
    EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
    UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
    TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
    INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
    UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
    UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
    UPDATE #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 #ServicesServiceStatus

/* -------------------------------------------------------------------------------------------------------------*/
DROP TABLE #ServicesServiceStatus                /*Perform cleanup*/
DROP TABLE #RegResult








Here is the screenshot after the execution of the script.

Labels:

Comments

Post a Comment

Popular posts from this blog

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql'

As huge a database grows, that tedious are regular DBA activities become. Besides, a warehouse database, can grow with no bounds to challenge.  There was a patching on one of our databases, SP3 for SQL Server 2008 R2, which was failing due to the error below. As the error says, MASTER the heart of SQL Server  “Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 2627, state 1, severity 14. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. Cannot recover the master database. SQL Server is unable to run. Restore master from a full
Post a Comment
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a
Post a Comment
Read more