June 13, 2013 at 3:43 pm
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.
use msdb
go
select
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
where
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
(
day,
dbo.agent_datetime(run_date, run_time),
getdate()
) <= 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.
June 14, 2013 at 6:04 am
xp_servicecontrol 'querystate', 'SQLSERVERAGENT'
June 14, 2013 at 8:03 am
Thanks! Your answer is just what I needed! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply