SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query to determine if SQL Agent is up?


Query to determine if SQL Agent is up?

Author
Message
aiki4ever-796329
aiki4ever-796329
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 95
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.
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2186 Visits: 2931
xp_servicecontrol 'querystate', 'SQLSERVERAGENT'
aiki4ever-796329
aiki4ever-796329
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 95
Thanks! Your answer is just what I needed! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search