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
Leave a Reply