How to Auto Check SQL Server Services or Essential Windows Services

You might have scheduled important jobs through SQL Server agent but they would have missed the schedule as the SQL Agent might have been turned off accidentally. You can be sure that this never happen again. If SQL Server services do not run properly then SQL Server database corruption might be a reason.

Start with the configuration table

Create a configuration table with columns “Servername”, “ServiceName” and “DesiredStatus” and fill the table with respective values. For example if you want the SQL Server agent service of SERVER1 to be in ‘Stopped’ status, then the configuration table should look like this

We will be using this configuration table to compare the state of service from the collected log and take an appropriate action.

Start collecting the log

We are going to use powershell to collect the service status from all our SQL Servers and store it in a table. The output of powershell is not properly formatted. So we will be creating a function to parse each row form the initial output and then split string based on the delimiter. In this case, the delimiter is the space ” “. For each string passed, the function would return 3 columns. The first column is the status of the service, second column is the name of the service and the last column is the display name of the service.

Compare log with the configuration table

Now we can easily compare the log table with the configuration table. If the status of a server’s service is not compliant, we can set an alert to the DBA team so that they can check and take an immediate action. We can also create an automated fix using net start or net stop command.

CREATE FUNCTION dbo.intocolumns (@sourcestring VARCHAR(MAX))
RETURNS @tableoutput TABLE (
    cstate [nvarchar](500)
    ,cname [nvarchar](500)
    ,cdspname [nvarchar](500)
    )
AS
BEGIN
    DECLARE @splitvalue NVARCHAR(255)
    DECLARE @fname VARCHAR(1000)
    DECLARE @c1 VARCHAR(100)
    DECLARE @c2 VARCHAR(100)
    DECLARE @c3 VARCHAR(1000)
    DECLARE @foundat INT
    DECLARE @count INT

    SET @count = 0

    WHILE CHARINDEX(' ', @sourcestring) > 0
    BEGIN
        SELECT @foundat = CHARINDEX(' ', @sourcestring)

        SELECT @splitvalue = SUBSTRING(@sourcestring, 1, @foundat - 1)

        IF @splitvalue <> ''
        BEGIN
            SET @count = @count + 1

            IF @count = 1
            BEGIN
                SET @c1 = @splitvalue
            END

            IF @count = 2
            BEGIN
                SET @c2 = @splitvalue
            END

            IF @count = 3
            BEGIN
                SET @fname = @splitvalue
            END

            IF @count > 3
            BEGIN
                SET @fname = @fname + ' ' + @splitvalue
            END
        END

        SELECT @sourcestring = SUBSTRING(@sourcestring, @foundat + 1, LEN(@sourcestring) - @foundat)
    END

    SET @fname = @fname + ' ' + @sourcestring
    SET @c3 = @fname

    INSERT INTO @tableoutput
    VALUES (
        @c1
        ,@c2
        ,@c3
        )

    RETURN
END
GO

CREATE TABLE ##PTBL1 (c1 VARCHAR(1000))

INSERT INTO ##PTBL1
EXEC xp_cmdshell 'powershell.exe -command "get-service -computer SERVERNAME"'

DELETE TOP (3)
FROM ##PTBL1

--select * from ##PTBL1
CREATE TABLE ##PTBL2 (
    c1 VARCHAR(200)
    ,c2 VARCHAR(200)
    ,c3 VARCHAR(1000)
    )

DECLARE CUR1 CURSOR
FOR
SELECT *
FROM ##PTBL1

DECLARE @value VARCHAR(1000)

OPEN CUR1

FETCH NEXT
FROM CUR1
INTO @value

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO ##PTBL2
    SELECT *
    FROM dbo.intocolumns(@value)

    FETCH NEXT
    FROM CUR1
    INTO @value
END

CLOSE CUR1

DEALLOCATE CUR1

SELECT *
FROM ##PTBL2

DROP TABLE ##PTBL2

DROP TABLE ##PTBL1

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst email damage and excel recovery software products. For more information visit www.datanumen.com