Find Currently Running Scheduled Job

  • Sioban Krzywicki

    One Orange Chip

    Points: 27769

    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

    Hall of Fame

    Points: 3312

    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

    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: 3618

    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

    SSC Guru

    Points: 61781

    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
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • Sioban Krzywicki

    One Orange Chip

    Points: 27769

    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

    SSC Guru

    Points: 61781

    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
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • ken.trock

    SSCertifiable

    Points: 5147

    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: 27769

    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

    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: 27769

    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

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

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