skreebydba

In my last post, I covered using xp_cmdshell to check the status of the SQL Server Agent service. I also mentioned my environment consists of more than 200 instances of SQL Server. The challenge I faced when developing this process was finding a way to check the Agent service on all instances from a central location. Fortunately, we have a central repository using linked servers to collect performance metrics. I will show how to use these linked servers to check Agent status. You can find the complete stored procedure code at the show source link at the bottom of the post. I will step through this code line by line.

The repository database has a table that contains all of the instances in the environment. The naming standard for linked servers in the environment is DUL + instance name. The following block of code selects all of the active instances names and prefixes them with DUL, inserting them into a temp table:

set @currentrow = 1

INSERT #serverlist
SELECT 'DUL' + dpc_sql_cluster_nm
FROM duddba_master.dbo.DUT_POD_SQL_SERVER
WHERE dpc_inuse_ind = 1
AND dpc_ismonitored_ind = 1

SET @totalrows = @@rowcount

The dpc_inuse_ind and dpc_ismonitored_ind flags allow instances to be excluded from monitoring in the event of maintenance. Setting @totalrows to the @@rowcount returned from the insert provides an upper limit for the loop that will run in the next step.

WHILE @currentrow <= @totalrows
BEGIN 

SET @servername = (SELECT TOP 1 servername FROM #serverlist)
SET @currentrow = @currentrow + 1

SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

INSERT INTO #processes
EXEC (@sqlstr)

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

INSERT INTO #status
VALUES
(SUBSTRING(@servername,4,8), @rowcount)

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END

The WHILE loop executes the code between the begin and end once for each row in the temp table, starting with @currentrow set to 1.

WHILE @currentrow <= @totalrows

The next two lines select the first instance out of the temp table, write it to variable @servername and increment the @currentrow counter.

SET @servername = (SELECT TOP 1 servername FROM #serverlist)
SET @currentrow = @currentrow + 1

The next three lines build a dynamic SQL string using an xp_cmdshell command to generate a list of services running on the instance. This dynamic SQL string is executed and the results are inserted into temp table #processes.

SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

INSERT INTO #processes
EXEC (@sqlstr)

Next, variable @rowcount is populated with the count of SQL Agent processes running on the machine. A non-zero count indicates the process is running.

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

In order to prevent an alert when the instance is starting up, the create date for tempdb and the datetime value from 5 minutes earlier are captured.

SELECT @crdate = crdate FROM master.sys.sysdatabases
WHERE name = 'tempdb'

SELECT @startup = DATEADD(minute,-5,getdate())

If the rowcount is 0 and the instance has been up for more than 5 minutes, an e-mail will be sent indicating the instance SQL Agent is not running on the instance. The REPLACE statement strips the DUL prefix from the instance name.

IF (@rowcount = 0 AND (@sixmin > @crdate))
BEGIN
DECLARE @body1 VARCHAR(100)
DECLARE @subject1 VARCHAR(100)
DECLARE @servermail VARCHAR(8)
SET @servermail = REPLACE(@servername,'DUL','')
SET @body1 = 'SQL Server Agent is Down on '+@servermail+ ' - Please Check'
SET @subject1 = 'SQL Server Agent is Down on ' + @servermail
EXEC msdb.dbo.sp_send_dbmail @recipients=henry@skreebydba.com',
@profile_name = 'DBASQLServerPublicProfile',
@subject = @subject1,
@body = @body1,
@body_format = 'HTML' ;
END 

The final code in the loop deletes the current instance name from the #serverlist table and clears out the #processes temp table.

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END 

Once the loop completes, the temp tables are cleaned up and the procedure finishes.

 
DROP TABLE #processes
DROP TABLE #serverlist

The combination of dynamic SQL within a loop is an extremely powerful tool. This example provides a template we've used for many different tasks in the enviroment. I hope you can put these ideas to use.

USE [DUDMON]
GO

/****** Object: StoredProcedure [dbo].[DUP_CHECK_SQL_AGENT] Script Date: 03/06/2012 11:40:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[DUP_CHECK_SQL_AGENT]
as

/*
DUP_CHECK_SQL_AGENT – Checks to verify that SQL Server Agent is running on all of the PODS servers. Queries the sysprocesses table to see if
the SQL Server Agent process is running. If it is not, an e-mail will be sent.
Parameters: None

Dependent procedures and Tables: None
Created by - Frank Gill
Last Updated by - Frank Gill
Last Updated - 17 May 2010
Version - 1
*/
SET NOCOUNT ON

BEGIN TRY
-- Declare local variables
DECLARE @servername VARCHAR(40)
DECLARE @sqlstr VARCHAR(2000)
DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @running INT
DECLARE @rowcount TINYINT

CREATE TABLE #processes
(processlist VARCHAR(4000))

CREATE TABLE #serverlist
(servername VARCHAR(40) not null)

CREATE TABLE #status
(linkedserver VARCHAR(8)
,[status] TINYINT)

set @currentrow = 1
-- Build list of valid linked servers
INSERT #serverlist
SELECT 'DUL' + dpc_sql_cluster_nm
FROM duddba_master.dbo.DUT_POD_SQL_SERVER
WHERE dpc_inuse_ind = 1
AND dpc_ismonitored_ind = 1


SET @totalrows = @@rowcount
-- Begin looping through the list of linked server values
WHILE @currentrow <= @totalrows
BEGIN 
--select @currentrow as CURRENTROW

SET @servername = (SELECT TOP 1 servername FROM #serverlist)

SET @currentrow = @currentrow + 1
SET @sqlstr = @servername + + '...xp_cmdshell ''tasklist'''

SELECT @sqlstr

INSERT INTO #processes
EXEC (@sqlstr)

SELECT @rowcount = COUNT(*) FROM #processes
WHERE processlist LIKE '%sqlagent%'

SELECT @crdate = crdate FROM master.sys.sysdatabases
WHERE name = 'tempdb'

SELECT @startup = DATEADD(minute,-5,getdate())

IF (@rowcount = 0 AND (@sixmin > @crdate))
BEGIN
--SELECT @servername AS [Server name]
--SELECT 'This stuff is broke!!!'
DECLARE @body1 VARCHAR(100)
DECLARE @subject1 VARCHAR(100)
DECLARE @servermail VARCHAR(8)
SET @servermail = REPLACE(@servername,'DUL','')
SET @body1 = 'SQL Server Agent is Down on '+@servermail+ ' - Please Check'
SET @subject1 = 'SQL Server Agent is Down on ' + @servermail
EXEC msdb.dbo.sp_send_dbmail @recipients='fgill@allstate.com',
@profile_name = 'DBASQLServerPublicProfile',
@subject = @subject1,
@body = @body1,
@body_format = 'HTML' ;
END 

DELETE FROM #serverlist WHERE servername = @servername
DELETE FROM #processes 

END 

DROP TABLE #status
DROP TABLE #processes
DROP TABLE #serverlist

END TRY

BEGIN CATCH 

INSERT INTO dbo.DUT_ERROR_LOG
SELECT 
@servername,
@status,
getdate(),
CURRENT_USER AS CurrentUser,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;

END CATCH
GO