Need a logic to query a sql job

Question

Need a logic to query a sql job

srawant

SSCommitted

Points: 1829
More actions
April 1, 2009 at 5:01 pm

#132765

Hi Guys,
I have several Jobs running with different schedule. For example one sql job runs every 15 mins. As we are busy with our daily jobs sometime with any means this job gets disabled it will not be running and we are unaware of the fact that the job is disabled. So, it has been couple of times that we got the job is not running after 2 days. So, to alert us with this fact that the job is disabled we want to automate a process to have a check job job that will basically check whether or not the job is running or not. How can I do this ? I may not require that it should tell me that at the regular 15 min it is not running but I may want that if the job is not running for an hour or two than it provides me an alert so that somebody or me can go and check the server and see whats going on.. If it is disabled than enable or find the reason of why it is disabled and so on...
Can you guys help me with this?? Will appreciate it..
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

You can query the msdb.dbo.sysjobactivity table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jetro Ten Centuries Points: 1257 More actions · Answer 2

Hi,

hmm..I think the script from this topic didn't help you 🙂

What was the problem with it?

Regards

-----------------------
SQL Server Database Copy Tool at Codeplex

srawant SSCommitted Points: 1829 More actions · Answer 3

HI SSC,

Actually I wanted to check for several servers and several jobs. The code you shared with me helped for only one job.

Thanks,

Jetro Ten Centuries Points: 1257 More actions · Answer 4

Hi,

to check more than one job you could read the job names from a table. Or you could define the jobs to check in the script and check one after the other.

To monitor more than one server you could implement this script on each of this servers 🙂

To have an overview over your running (or not running) jobs you could use Idera's sql job manager => Here

I hope this helps.

-----------------------
SQL Server Database Copy Tool at Codeplex