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


Current Running Job Schedule Id or Name


Current Running Job Schedule Id or Name

Author
Message
ganeshmuthuvelu
ganeshmuthuvelu
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 305
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



GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
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
ganeshmuthuvelu
ganeshmuthuvelu
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 305
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.



GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
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
ganeshmuthuvelu
ganeshmuthuvelu
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 305
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



ganeshmuthuvelu
ganeshmuthuvelu
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 305
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



Derek Robinson
Derek Robinson
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 116
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.
x_japanfans5312
x_japanfans5312
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 209
Hi, i also facing this problem, is there any solution for this ?
MusabUmair
MusabUmair
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 619
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 @State INT
DECLARE @Job_Name nVARCHAR(1000)

SET @Job_Name = 'Your Job Name Goes Here'

SELECT @Job_ID = job_id
FROM msdb.dbo.SysJobs
WHERE name = @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
where job_id = @Job_ID

SELECT ISNULL(@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
h.brotherton
h.brotherton
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 59
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 )
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