Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to determine if SQL Agent is up? Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 3:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:07 AM
Points: 9, Visits: 82
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.
Post #1463331
Posted Friday, June 14, 2013 6:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:29 AM
Points: 1,378, Visits: 2,684
xp_servicecontrol 'querystate', 'SQLSERVERAGENT'
Post #1463501
Posted Friday, June 14, 2013 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:07 AM
Points: 9, Visits: 82
Thanks! Your answer is just what I needed!
Post #1463585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse