To check for recent failures of maintenance jobs, a person can run a query such as
-- Identify SQL Agent jobs (and maintenance plan subplans) that have failed in the last few days.
-- If the Agent is not running, jobs will not start and this query will not find any problems.
convert(char(13),server ) as Server ,
convert(char(60),name ) as JobName ,
convert(char( 7),step_id ) as StepNum ,
convert(char(35),step_name) as StepName ,
dbo.agent_datetime(run_date, run_time) as RunDateTime
from sysjobhistory, sysjobs
sysjobhistory.job_id = sysjobs.job_id and -- join tables
step_id <> 0 and -- do not select job overview, only the details
run_status <> 1 and -- select failed jobs
datediff -- last few days
) <= 8
order by Server, JobName, StepNum, StepName, RunDateTime desc -- most recent jobs first
That's all very nice but does not help much if the Agent is down and jobs don't even start running. Is it possible to run a T-SQL query to determine if the agent service is running? If you've got a lot of database servers to check, it would sure be convenient to run a query on all of them to check agent jobs and another query to make sure the agent service is up.