Find Currently Running Scheduled Job

Question

Find Currently Running Scheduled Job

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
April 24, 2013 at 11:43 pm

#274523

Comments posted to this topic are about the item Find Currently Running Scheduled Job
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 11 posts - 1 through 10 (of 10 total)

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

karthik babu Hall of Fame Points: 3335 More actions · Answer 1

Hi Stefan,

I am using the below command just to monitor currently running scheduled job since the output will reflect very late in another table.

EXEC msdb.dbo.sp_help_job @execution_status = 1

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 2

Hi,

You could also use the sp_helpjobactivity for a start_execution_date and no stop_execution_date for that info perhaps?

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

LightVader Hall of Fame Points: 3810 More actions · Answer 3

I have a similar "job killer" task. I used sysjobs, sysjobactivity, and sysjobhistory (all in MSDB) to get the information about currently executing jobs. I also have a table to hold the job name and what point the job should be killed.

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

Jared SSC Guru Points: 61793 More actions · Answer 4

I guess I just don't get it. You said you did not want to use sp_help_job because you had to insert into a table, yet you do the same here. Why are we reinventing the wheel?

Jared
CE - Microsoft

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 5

SQLKnowItAll (4/25/2013)
I guess I just don't get it. You said you did not want to use sp_help_job because you had to insert into a table, yet you do the same here. Why are we reinventing the wheel?

Part of what I wanted to avoid was guessing at what data types I was pulling back. I also don't like doing blind table creation. This gives us a better idea of what we're actually pulling back and lets us get closer to the source, querying more just what we need.

Also, much of this article is an exercise in how to look into the system queries, find what they're doing and access what you need.

I guess it isn't so much "reinventing the wheel" as it is using the most appropriate wheel. You don't want a tractor tire on a shopping cart.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Jared SSC Guru Points: 61793 More actions · Answer 6

Stefan Krzywicki (4/25/2013)
SQLKnowItAll (4/25/2013)
I guess I just don't get it. You said you did not want to use sp_help_job because you had to insert into a table, yet you do the same here. Why are we reinventing the wheel?
Part of what I wanted to avoid was guessing at what data types I was pulling back. I also don't like doing blind table creation. This gives us a better idea of what we're actually pulling back and lets us get closer to the source, querying more just what we need.
Also, much of this article is an exercise in how to look into the system queries, find what they're doing and access what you need.
I guess it isn't so much "reinventing the wheel" as it is using the most appropriate wheel. You don't want a tractor tire on a shopping cart.

Well said!

Jared
CE - Microsoft

ken.trock SSCertifiable Points: 5147 More actions · Answer 7

We have data retention jobs that hang sometimes and decided to do something similar; automatically kill them at a certain point.

SELECT * INTO #JobInfo

FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'

, 'set fmtonly off exec msdb.dbo.sp_help_job')

--Don't let this accidently run between 7pm and 4am, legitimate times for data retention

IF DATEPART(hh, GETDATE()) >= 19 OR DATEPART(hh, GETDATE()) <= 4

RETURN

DECLARE cRetentionJobs CURSOR FOR

select name

from #JobInfo

where current_execution_status <> 4 --anything not idle

AND name LIKE '%DataRetention%' AND [enabled] = 1

We'll run thru this cursor and exec msdb..sp_stop_job for each.

But we have the convenience knowing that there's only one schedule for each of these jobs.

Thanks for the article.

Ken

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 8

ken.trock (4/25/2013)
We have data retention jobs that hang sometimes and decided to do something similar; automatically kill them at a certain point.
SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')
--Don't let this accidently run between 7pm and 4am, legitimate times for data retention
IF DATEPART(hh, GETDATE()) >= 19 OR DATEPART(hh, GETDATE()) <= 4
RETURN
DECLARE cRetentionJobs CURSOR FOR
select name
from #JobInfo
where current_execution_status <> 4 --anything not idle
AND name LIKE '%DataRetention%' AND [enabled] = 1
We'll run thru this cursor and exec msdb..sp_stop_job for each.
But we have the convenience knowing that there's only one schedule for each of these jobs.
Thanks for the article.
Ken

It is a lot easier when there's only one scheduled time per job. Glad you liked it.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Nice article, Stefan. Thanks for getting it organized. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 10

Thanks!

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams