Current Running Job Schedule Id or Name

  • Hello:

    I have a scheduled job running in SQL Server. While the job is running, is it possible to find out the schedule name or ID which made the job to run?

    Thanks,

    Ganesh

  • msdb.dbo.sysjobschedules and msdb.dbo.sysschedules should have what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks but that contains all the schedules. I want to know under what schedule the current job is running..

    For example, assume that I have a TSQL job that has 3 schedules S1, S2 and S3 - and the job is running currently becos of Schedule "S3" - I need to get that name "S3" .

    Hope the question is clear now.

    Thanks.

  • Ah. I get it now.

    Using the schedule table and the sysjobhistory table, you could probably join them together and get the data that way. It's not going to be all that easy.

    Here's a query I have that breaks down jobs by a summary of their schedule. You might be able to modify it so that it will give you a table of all the scheduled job starts by date and time, separated by schedule, and then join that to the log table, and get which schedule started it. It'll take some work to get that, but this should get you going in the right direction.

    CREATE TABLE #Days (

    ID INT PRIMARY KEY,

    Days VARCHAR(100)) ;

    ;

    WITH Numbers

    AS (SELECT

    0 AS Number

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    2

    UNION ALL

    SELECT

    3

    UNION ALL

    SELECT

    4

    UNION ALL

    SELECT

    5

    UNION ALL

    SELECT

    6),

    Base

    AS (SELECT

    POWER(2, number) Num

    FROM

    Numbers

    UNION ALL

    SELECT

    0),

    Combos

    AS (SELECT

    B1.Num B1,

    B2.Num B2,

    B3.Num B3,

    B4.Num B4,

    B5.Num B5,

    B6.Num B6,

    B7.Num B7,

    B1.Num + B2.Num + B3.Num + B4.Num + B5.Num + B6.Num + B7.Num AS Total

    FROM

    Base B1

    INNER JOIN Base B2

    ON B1.Num > B2.Num

    INNER JOIN Base B3

    ON B2.Num > B3.Num

    OR B2.Num = 0

    AND B3.Num = 0

    INNER JOIN Base B4

    ON B3.Num > B4.Num

    OR B3.Num = 0

    AND B4.Num = 0

    INNER JOIN Base B5

    ON B4.Num > B5.Num

    OR B4.Num = 0

    AND B5.Num = 0

    INNER JOIN Base B6

    ON B5.Num > B6.Num

    OR B5.Num = 0

    AND B6.Num = 0

    INNER JOIN Base B7

    ON B6.Num > B7.Num

    OR B6.Num = 0

    AND B7.Num = 0)

    INSERT INTO

    #Days (ID, Days)

    SELECT

    Total,

    STUFF((SELECT

    ',' + CASE Val

    WHEN 1 THEN 'Sunday'

    WHEN 2 THEN 'Monday'

    WHEN 4 THEN 'Tuesday'

    WHEN 8 THEN 'Wednesday'

    WHEN 16 THEN 'Thursday'

    WHEN 32 THEN 'Friday'

    WHEN 64 THEN 'Saturday'

    END

    FROM

    (SELECT

    Total,

    B1,

    B2,

    B3,

    B4,

    B5,

    B6,

    B7

    FROM

    Combos) C UNPIVOT ( Val FOR Base IN (B1, B2, B3, B4,

    B5, B6, B7) ) as Unpvt

    WHERE

    Val > 0

    AND Total = Main.Total

    ORDER BY

    Val

    FOR

    XML PATH('')), 1, 1, '')

    FROM

    Combos Main

    ORDER BY

    Total ;

    SELECT

    Name,

    (SELECT

    Name,

    CONVERT(VARCHAR(50), CAST(STUFF(STUFF(active_start_date, 5, 0, '-'), 8,

    0, '-') + ' '

    + STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(6)),

    6), 3, 0, ':'), 6, 0, ':') AS DATETIME), 100) AS FirstRun,

    CONVERT(VARCHAR(50), CAST(STUFF(STUFF(active_end_date, 5, 0, '-'), 8,

    0, '-') + ' '

    + STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(6)), 6),

    3, 0, ':'), 6, 0, ':') AS DATETIME), 100) AS FinalRun,

    CASE freq_type

    WHEN 1 THEN 'One-Time'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN 'Weekly'

    WHEN 16 THEN 'Monthly'

    WHEN 32 THEN 'Monthly'

    WHEN 64 THEN 'SQL Agent Start'

    WHEN 128 THEN 'System Idle'

    END AS Frequency,

    CASE freq_type

    WHEN 1 THEN NULL

    WHEN 4

    THEN 'Every ' + CAST(freq_interval AS VARCHAR(10)) + ' Day(s)'

    WHEN 8 THEN (SELECT

    Days

    FROM

    #Days

    WHERE

    ID = Schedule.freq_interval)

    WHEN 16

    THEN 'On day ' + CAST(freq_interval AS VARCHAR(10)) + ' of every '

    + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' Month(s)'

    WHEN 32

    THEN 'On the ' + CASE freq_relative_interval

    WHEN 1 THEN '1st'

    WHEN 2 THEN '2nd'

    WHEN 4 THEN '3rd'

    WHEN 8 THEN '4th'

    WHEN 16 THEN 'Last'

    END + ' ' + CASE freq_interval

    WHEN 1 THEN 'Sunday'

    WHEN 2 THEN 'Monday'

    WHEN 3 THEN 'Tuesday'

    WHEN 4 THEN 'Wednesday'

    WHEN 5 THEN 'Thursday'

    WHEN 6 THEN 'Friday'

    WHEN 7 THEN 'Saturday'

    END + ' of every '

    + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' Month(s)'

    END AS Days,

    CASE freq_subday_type

    WHEN 1 THEN 'Once'

    WHEN 2

    THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10))

    + ' Second(s)'

    WHEN 4

    THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10))

    + ' Minute(s)'

    WHEN 8

    THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(10))

    + ' Hour(s)'

    END AS Timing

    FROM

    msdb.dbo.sysschedules Schedule

    INNER JOIN msdb.dbo.sysjobschedules JobSchedule

    ON Schedule.schedule_id = JobSchedule.schedule_id

    WHERE

    job_id = Job.job_id

    FOR

    XML AUTO,

    TYPE) AS Schedules

    FROM

    msdb.dbo.sysjobs Job

    FOR

    XML AUTO,

    TYPE;

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Is there any other way to know the running schedule?. The suggestion does not seem to work for me.

    Again, the goal is to get the schedule name or schedule_id that started the job.

    Thanks,

    Ganesh

  • Can someone please help me here.

    I want to know under what schedule the current job is running.. For example, assume that I have a TSQL job that has 3 schedules S1, S2 and S3 - and the job is running currently becos of Schedule "S3" - I need to get that name "S3" or "scheduled_id" while the step is currently executing,

    I need to know if it is possible or not. All the tables currently show only the next_schedule_id but I need to know the current_schedule_id under which the job is running..

    Any help will be appreciated.

    Thanks,

    Ganesh

  • Hi Ganesh

    I know this was some time ago but I have the same problem and I'm wondering if you ever found a solution. If you have, please share, if not, then the links below may help.

    So far I've found these ideas but haven't had a chance to test them out yet

    http://manjunathcbhat.wordpress.com/2011/04/24/get-information-on-all-jobs-running-on-a-sql-server-instance/

    http://www.sqlservercentral.com/Forums/Topic768256-149-2.aspx

    regards

    Derek

    ps Good luck with the whole elephant God thing.

  • Hi, i also facing this problem, is there any solution for this ?

  • This will give you the name of the login or schedule who invoked the job. I hope this will help

    DECLARE @Schedule_Name_ID nVARCHAR(1000)

    DECLARE @Job_ID nVARCHAR(100)

    DECLARE @StateINT

    DECLARE @Job_Name nVARCHAR(1000)

    SET@Job_Name = 'Your Job Name Goes Here'

    SELECT@Job_ID = job_id

    FROMmsdb.dbo.SysJobs

    WHEREname = @Job_Name

    IF(@Job_ID IS NOT NULL)

    BEGIN

    CREATE TABLE #job_current_state

    (

    Job_ID UNIQUEIDENTIFIER,

    Last_Run_Date INT,

    Last_Run_Time INT,

    Next_Run_Date INT,

    Next_Run_Time INT,

    Next_Run_Schedule_ID INT,

    Requested_To_Run INT,

    Request_Source INT,

    Request_Source_ID VARCHAR(100),

    Running INT,

    Current_Step INT,

    Current_Retry_Attempt INT,

    State INT

    )

    INSERT INTO #job_current_state

    EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage

    SELECT @Schedule_Name_ID = Request_Source_ID,@State = State

    FROM #job_current_state

    wherejob_id = @Job_ID

    SELECTISNULL(@Schedule_Name_ID,'NA') Schedule_Name_ID

    ,CASE @State WHEN 1 THEN 'Running' ELSE 'Stopped' END State

    DROP TABLE #job_current_state

    END

    ELSE

    BEGIN

    SELECT'Job does not exist' output

    END

    Musab
    http://www.sqlhelpline.com

  • So far this works for me. Using tokens....

    SET @myJobName= ( SELECT name FROM msdb.dbo.sysjobs WHERE Job_ID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) )

    SET @lastRunDate = $(ESCAPE_NONE(STRTDT))

    SET @lastRunTime = $(ESCAPE_NONE(STRTTM))

    SET @myScheduleName = ( SELECT scheds.name FROM msdb..sysjobs AS jobs

    LEFT JOIN msdb..sysjobschedules AS jobscheds ON jobs.job_id = jobscheds.job_id

    LEFT JOIN msdb..sysschedules AS scheds ON jobscheds.schedule_id = scheds.schedule_id

    WHERE jobs.job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))) and active_start_time = @lastRunTime )

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

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