October 19, 2012 at 9:01 am
I need to know if a job is currently running and by digging through msdb.dbo.sp_help_job I have some code that lets me find out. I want to use this information in a stored procedure, so I can't just execute msdb.dbo.sp_help_job.
The problem I'm having is that I also want to know what schedule kicked off the job this time. If I have 5 distinct schedules in the job, I want to know which one kicked this off by name. I can get the schedule information from msdb.dbo.sysjobschedules and msdb.dbo.sysschedules, but there doesn't seem to be a current schedule id in xp_sqlagent_enum_jobs, which I'm using to get other information about the currently running job. I can see the next run schedule id, but not the current one.
Anyone know where I can find the current one?
Also, I'm not sure how to open master.dbo.xp_sqlagent_enum_jobs to examine what it does. Heck, I'm not sure I can find it since the closest thing I can find in master is sys.xp_sqlagent_enum_jobs under Extended Stored Procedures. When I right-click on it, I can Start Power Shell, but I can't script the stored procedure. Will Start Power Shell run the sp or open it for editing?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 10:23 am
Stepan, it looks like msdb.dbo.sysjobactivity might have what you're looking for.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 19, 2012 at 10:25 am
Did you try SysJobActivity?
October 19, 2012 at 10:29 am
While sysjobactivity doesn't have the schedule_id, I might be able to match run_requested_date against sysjobschedules and sysschedules to figure it out. Thanks both of you, worth looking into.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 10:31 am
You're welcome. BTW, sysjobhistory's run_date and run_time records when the job started, so that might also help you hook it up to a schedule.
October 19, 2012 at 10:38 am
Ugh, you know you've been working on something too long when you can recognize the GUID of the job you're running.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 10:39 am
October 19, 2012 at 10:55 am
Oh for goodness sake.
next_run_schedule_id is the currently running job if it hasn't completed yet.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 11:28 am
Are you sure next_run_schedule is correct even if another schedule matures when the job is already running having been started by a different schedule? The job won't start when that happens, an activation occurring while the job is already running, whether from the same of a different schedule, gets thrown away not actioned, but maybe next_run_schedule gets updated anyway - I don't know.
Tom
October 19, 2012 at 11:40 am
L' Eomot Inversé (10/19/2012)
Are you sure next_run_schedule is correct even if another schedule matures when the job is already running having been started by a different schedule? The job won't start when that happens, an activation occurring while the job is already running, whether from the same of a different schedule, gets thrown away not actioned, but maybe next_run_schedule gets updated anyway - I don't know.
Good point, I'll test that now.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 11:50 am
L' Eomot Inversé (10/19/2012)
Are you sure next_run_schedule is correct even if another schedule matures when the job is already running having been started by a different schedule? The job won't start when that happens, an activation occurring while the job is already running, whether from the same of a different schedule, gets thrown away not actioned, but maybe next_run_schedule gets updated anyway - I don't know.
Yup, still correct. If First Run starts the process and Second Run is supposed to start while First Run is still going, the query reports First Run even after Second Run should have started, but didn't.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 19, 2012 at 11:51 am
That's good to know. Thanks for reporting it.
October 19, 2012 at 12:39 pm
Brandie Tarvin (10/19/2012)
That's good to know. Thanks for reporting it.
Yes, very good to know. Thanks Stefan.
Tom
October 19, 2012 at 12:40 pm
I may write this all up as an article or two.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply