August 4, 2009 at 11:05 am
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
August 4, 2009 at 11:23 am
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
August 4, 2009 at 11:27 am
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.
August 4, 2009 at 11:38 am
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
August 5, 2009 at 8:46 am
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
August 10, 2009 at 8:43 am
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
May 31, 2011 at 1:04 pm
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://www.sqlservercentral.com/Forums/Topic768256-149-2.aspx
regards
Derek
ps Good luck with the whole elephant God thing.
October 7, 2012 at 8:40 am
Hi, i also facing this problem, is there any solution for this ?
October 28, 2013 at 2:44 am
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
July 15, 2015 at 8:16 am
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