Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Currently Running Scheduled Job


Find Currently Running Scheduled Job

Author
Message
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
karthik babu
karthik babu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 823
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
Ness
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 974
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
LightVader
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 2887
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.
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
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?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
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!

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
ken.trock
ken.trock
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 1713
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
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 8067
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
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
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