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 12»»

Find Currently Running Scheduled Job Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 11:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 866, Visits: 7,497
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
Post #1446303
Posted Thursday, April 25, 2013 12:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 5:49 AM
Points: 586, Visits: 574
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
Post #1446313
Posted Thursday, April 25, 2013 8:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 11, 2014 3:33 AM
Points: 179, Visits: 840
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
Post #1446530
Posted Thursday, April 25, 2013 9:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 763, Visits: 2,284
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.
Post #1446563
Posted Thursday, April 25, 2013 10:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
Post #1446622
Posted Thursday, April 25, 2013 11:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 866, Visits: 7,497
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
Post #1446626
Posted Thursday, April 25, 2013 11:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
Post #1446642
Posted Thursday, April 25, 2013 12:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:29 PM
Points: 323, Visits: 1,475
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
Post #1446674
Posted Thursday, April 25, 2013 12:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 866, Visits: 7,497
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
Post #1446675
Posted Thursday, April 25, 2013 5:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1446755
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse