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

Current Running Job Schedule Id or Name Expand / Collapse
Author
Message
Posted Tuesday, August 4, 2009 11:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
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



Post #765011
Posted Tuesday, August 4, 2009 11:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #765017
Posted Tuesday, August 4, 2009 11:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
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.



Post #765023
Posted Tuesday, August 4, 2009 11:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #765028
Posted Wednesday, August 5, 2009 8:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
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



Post #765535
Posted Monday, August 10, 2009 8:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
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



Post #767923
Posted Tuesday, May 31, 2011 1:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:25 AM
Points: 45, Visits: 114
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.
Post #1117695
Posted Sunday, October 7, 2012 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:06 AM
Points: 10, Visits: 209

Hi, i also facing this problem, is there any solution for this ?
Post #1369535
Posted Monday, October 28, 2013 2:44 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 1:37 AM
Points: 65, Visits: 528
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
Post #1508774
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse