Invoked Schedule Id of the Job : Urgent!

Question

Post reply

Invoked Schedule Id of the Job : Urgent!

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
August 10, 2009 at 4:21 pm

#133806

Hello:
When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the job. For example, a job could have multiple schedules "S1", "S2", "S3" etc., and assuming that the job has a TSQL job step in it, when executing the TSQL job step I need to determine which schedule_id that caused this job to be invoked (to run).
Is there a way to determine this from TSQL. Please help as this is badly needed.
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can you explain what you are looking for or why it is important to do this?

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 2

I have an reporting application to which the users subscribe to have reports automatically emailed to them. For example, user1 will subscribe for "Report1" to be emailed to him "every 1 hour", user2 will subscribe for "Report2" to be emailed "every 2 hours".

The reporting application will be a custom .exe that will be one of the job steps in SQL Server Agent. The job will have multiple schedules such as "every 1 hour", "every 2 hours", "every day" and so on.

So, when the job runs on the "every 1 hour" schedule, the application has to look for all users who have subscribed for reports "every 1 hour" and email them the reports. Similarly, when the job runs on the "every 2 hours" schedule, the application has to look for all users who have subscribed for reports "every 2 hours" and email them the reports.

It is therefore important for the reporting application to know under which schedule the job is running (or which schedule invoked the job) so that the application can lookup for the reports and users who had subscribed for that schedule.

I hope this helps you understand my requirement.

Thanks for any help provided.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 3

Any help on this will be greatly appreciated.

Thanks.

winash SSChampion Points: 11446 More actions · Answer 4

You can try the sp_help_jobschedule stored procedure(if this gives you what you were looking for)...

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 5

sp_help_jobschedule provides only the list of schedules for a job. But that's not what I wanted.

I want to know under which schedule the job is currently running. A job can have any number of schedules and I want to know which schedule invoked the job that is currently running.

Please help!

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Assumptions:

1. Single Reporting Job: Report Job

2. Multiple Schedules:

Schedule 1, every hour between 6:00 AM and 6:00 PM (i.e 6:00, 7:00, 8:00,...)

Schedule 2, every two hours between 6:00 Am and 6:00 PM (i.e. 6:00, 8:00, 10:00,...)

The job will only run once at 6:00, 7:00, 8:00, 9:00, 10:00, etc. How are you going to know what to send if the same job is run by both schedules? Only one of the schedules will actually fire the job, and I have no idea which. I have a full backup job that is run by two schedules, one that runs the backup once a week, and another on the first of the month. Sometimes the two are the same day. All I know, and care about in this case, is that the job runs, and it does with out failure.

Not sure how you will accomplish this as your requirements are, unfortunately, still a little vague to allow me to really help.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 7

Thanks for the reply. The schedules that I mentioned are just examples. In reality, these schedules will be such that are they are non overlapping. For example, schedule S1 will run every 1 hour on the 20th minute and schedule S2 will run every 2 hours on the 40th minute and so on.

This is how I have the data stored in the database table that has information about what reports to be emailed to users and the schedules.

User1 > Report1 > Schedule (S1)

User1 > Report2 > Schedule (S2)

User2 > Report4 > Schedule (S2)

....

User4 > Report1 > Schedule (S1)

So, when the job runs and if I know that schedule "S1" invoked it, then I will know that - Report1 has to be emailed to User1 and User2.

I hope this makes it clear why I need to know the schedule that invoked the job.

Please help!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Let us throw in another monkey into the wrench. What happens if the job is started at 20 minutes after the hour, and for various reasons ends up running until 45 minutes after the hour? Since the job is running when it is supposed to run at 40 minutes after the hour, it won't.

Please, don't tell me that this will never happen, as the first time it does what then? You need to plan for the possibility.

I'd recommend separate jobs and code each job appropriately.

On the other side, it does appear that you should be able to determine which schedule invoked the job, as I have seen it reported in the history for my backup job. I just haven't figured out how it does it yet. I'll do some more research tonight when I have a bit more time.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 9

I do understand that possibility but I would like to hear from you on determining the invoked schedule_id of the job. Please do understand that I do need to know the schedule_id while the job is running and not after it is finished, since it will not be of any use.

I do see that the sysjobhistory table does contain the schedule_id of the invoked job but that history table gets the data only after the job is completed, not when it being run.

I eagerly wait for your response.

Thanks.

winash SSChampion Points: 11446 More actions · Answer 10

I seem to understand what you are looking for...however I can't find any tables that store the schedule id from which a job is invoked...

There is a table - msdb.dbo.sysjobactivity - that stores a record for each job during the job execution (as long as the stop_execution_date is not null for a job in this table the job is currently executing).

I tried to see if there is any way to get the schedule that invoked the job when it is being executed but couldn't find anything - except for the horrible hacked code below which I am only attaching as I worked on it for the past hour 😛

The result of this code (best viewed by setting results to text) will display the name of the schedule and schedule id the job is invoked from (while the job is running)

And finally - I have no clue if this is going to help you in any way... :ermm:

/*

IMPORTANT: Creates a job called TestJob1 (and deletes any existing job with that name first)

This job will keep running at intervals of 10 and 23 seconds

This code will keep running

Stopping the code will not delete the job - it will still be running - delete the job after stopping the execution

*/

-- make the job wait for the specified delay and then do some very

-- meaningful thing like selecting the current date

DECLARE @JobSql nvarchar(max),@Delay varchar(12);

SET @Delay = '00:00:05';

SET @JobSql = 'WAITFOR DELAY '+QUOTENAME(@Delay,'''');

SET @JobSql = @JobSql + CHAR(13)+CHAR(10)+'SELECT getdate()';

DECLARE @JobCmd varchar(200) = @JobSql;

-- SET YOUR SERVER NAME HERE!!!

DECLARE @srvname sysname = '<>';

IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'TestJob1')

BEGIN

PRINT 'Deleting job';

EXEC msdb.dbo.sp_delete_job @job_name = 'TestJob1';

END

PRINT 'Creating job';

EXEC msdb.dbo.sp_add_job @job_name = 'TestJob1',@owner_login_name='sa';

EXEC msdb.dbo.sp_add_jobstep @job_name='TestJob1',@step_name = 'Step1',@command=@JobCmd;

EXEC msdb.dbo.sp_add_jobserver @job_name='TestJob1',@server_name=@srvname;

-- 10 second interval schedule

EXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched10Second',

@enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=10;

-- 23 second interval schedule

EXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched23Second',

@enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=23;

DECLARE @jobname sysname,@jobid uniqueidentifier,@joboutcomemsg nvarchar(1024);

SET @jobname = 'TestJob1';

SELECT @jobid = job_id FROM msdb.dbo.sysjobs WITH (NOLOCK) WHERE name = @jobname;

WHILE(1=1)

BEGIN

WHILE EXISTS(SELECT 1 FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid)

BEGIN

IF ((SELECT stop_execution_date FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid )IS NOT NULL)

BEGIN

IF (@joboutcomemsg IS NOT NULL)

SELECT 'Before Break',@joboutcomemsg JobOutcomeMessage;

WAITFOR DELAY '00:00:01'

BREAK -- job has completed - break out

END

ELSE

BEGIN

SELECT @joboutcomemsg = last_outcome_message FROM msdb.dbo.sysjobactivity T1 INNER JOIN msdb.dbo.sysjobservers T2 ON

T1.job_id = T2.job_id

WHERE T1.job_id = @jobid

IF (@joboutcomemsg IS NOT NULL)

SELECT 'During continue',@joboutcomemsg JobOutcomeMessage;

WAITFOR DELAY '00:00:01'

CONTINUE -- continue waiting

END

END;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

I didn't forget, I just spent most of last night figuring out a problem I was having here at work. Hopefully tonight I may get some time to look into this.

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 12

Thanks for the update.. I will wait to hear from you.

x_japanfans5312 SSC-Addicted Points: 458 More actions · Answer 13

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

pateldhruvb Grasshopper Points: 10 More actions · Answer 14

Hi, Have you find solution to get schedule ID when its running as I have facing exact same problem,you were facing.

can you please share your solution?

Thanks in advance,