Misleading 'Is Agent Running' and 'Agent Jobs' statuses.

[JoeVizcaino]

From a previous discussion, I understand you use SQLAgent - Generic Refresher to test if SQL Agent Service is online.
If SQLAgent - Generic Refresher is not present in sys.dm_exec_sessions, DBADash reports it as not running and Agent Jobs also as not running.

I am looking at two servers right now in that state. However, the Agent is in fact running, and the jobs are firing at the scheduled time, all with no issues. No entries in SQL/Agent logs (I know the fix is to restart the Agent Service).

My question (and suggestion) is how do you determine the Agent Jobs state of Not Running? And, if the SQLAgent - Generic Refresher is not present, maybe do an additional step to check the actual state of the Agent Service. If the agent is running, I feel the alert should be yellow, a warning that a possible issue might be in play, not that the agent is not running (which is false). A hung state for the Generic Refresher does not a true indication of jobs not running and/or the agent is not running.

maybe I am going about this the wrong way, I feel the agent jobs status should not be tied to Is agent running at least not without actually checking.

[mpaivant]

Hello! Congratulations on your great work!
I have the same problem as JoeVizcaino.

[DavidWiseman]

DBA Dash uses this query to check if the SQL Agent is running:

SELECT @IsAgentRunning = CASE WHEN EXISTS(SELECT * 
											FROM sys.dm_exec_sessions
											WHERE program_name = 'SQLAgent - Generic Refresher'
										)
							THEN CAST(1 AS BIT)
							ELSE CAST(0 AS BIT)
							END

I've found this reliable, but I'm open to suggestions on how to improve it. Previously this was used:

	SELECT @IsAgentRunning = CASE WHEN status=4 THEN 1 ELSE 0 END
	FROM   sys.dm_server_services dss
	WHERE  dss.[servicename] LIKE N'SQL Server Agent (%'

It was changed to use the generic refresher for the following reasons:

Checking for 'SQLAgent - Generic Refresher' in sys.dm_exec_sessions seems to be a better check than using sys.dm_server_services:
* It works on older SQL Server instances
* It can sometimes detect a problem where the agent is reported as running in sys.dm_server_services but is not actually firing jobs.

Note: It's collected as part of the ServerExtraProperties collection which runs every 1hr by default. This could be a reason the status doesn't match up. The schedule can be updated if required and it can also be triggered on demand.

Sorry for not responding to this post earlier.

[mpaivant]

Hi! David Wiseman,

Thanks for the reply.

My suggestion is something that is for all versions of SQLServer

USE msdb;
GO

SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM sys.dm_server_services
WHERE servicename = 'SQL Server Agent (MSSQLSERVER)'
AND status_desc = 'Running'
)

THEN 1
ELSE 0
END AS [SQL Server Agent Status];
GO

Thanks a lot.

[JoeVizcaino]

I understand why the check is being done on the 'SQLAgent - Generic Refresher' program. When the SQL Agent Service starts it kicks off various tasks, one of which is 'SQLAgent - Generic Refresher' (refresher). The refresher is all about job caching, this includes job activity, schedules and alerts. It is possible for the SQL Agent to be running but not the refresher. In this case YOU MIGHT experience job failures. And that MIGHT is the reason for my original post. The 'SQLAgent - Generic Refresher' not running does not mean the agent is not running. The refresher can be off one minute and on the next; another reason to raise a warning vs an offline.

What we do is we check both, the SQL Agent status and the refresher. If the agent is on, we check the refresher, if that is not on we raise an warning alert.

[DavidWiseman]

I might consider changing it to check both and display a warning if the generic refresher isn't running. I've never seen a situation where the generic refresher isn't running and jobs are still executing. Do you know how I could create this situation in my lab environment?

sys.dm_server_services isn't available on SQL2008 and SQL2005 which are still supported by DBA Dash but I can workaround that. sys.dm_server_services doesn't return any rows on a managed instance - don't think you actually have access to stop it though. The generic refresher check works on managed instance.

[JoeVizcaino]

One scenario comes to mind. Someone here killed all the long running sessions on multiple servers. That action killed the generic refresher sessions. DBADash reported SQL Agent Service as not running. All the jobs continued to work without issues. I do not know how or if it affected alerts. We were able to replicate this multiple times. This is what I use to check the Agent status. SELECT @@ServerName AS ServerName, CASE WHEN NOT EXISTS ( SELECT * FROM sys.dm_exec_sessions WHERE program_name = 'SQLAgent - Generic Refresher' ) THEN CASE WHEN ( SELECT status_desc FROM sys.dm_server_services WHERE servicename LIKE '%agent%' ) = 'Running' THEN 'Possible Issue. Restart the SQL Agent Service.' ELSE 'Not Running. Start the SQL Agent Service' END ELSE 'Running' END AS Status; Your DBADash application is outstanding. Great job with it. I use it daily.

[DavidWiseman]

That makes sense - I've done a quick test and it seems like jobs continue to work after killing the session. Interestingly it comes back if you create a new job after killing the session. I can see people having something to kill off long running sessions being a cause for this. I still think it's something to be alerted on but as you said it's probably better to report separate from the running status.

Thanks

[JoeVizcaino]

That is correct. A new job or any changes to a job brings it back. Hence the need for a warning and not an offline status alert.