Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I've been dealing with the Microsoft SQL Server Agent with regards to scheduling jobs.

I'm trying to find out what the next scheduled run date for a bunch of jobs that were created where the job ids from the msdb sysjobs table(s) joins back to my specific system tables.

We store the job ids that get created in sql server agent in our own system table in order to join back.

I've now discovered the sysjobsactivity table. It depends on a session. I may have a job that had a next scheduled run date in an earlier session, but a NULL value for that field in the latest session.

Restarting sql agent is at least one way to create a new session id. It looks like all existing jobs get an entry associated to the new session but the next scheduled run date doesn't necessarily move forward.

I'm pretty sure if I update a job with criteria that places all occurrences in the past, the next run date will be NULL, which makes sense.

But I do see cases where I have a recurring schedule with no end date, and the next scheduled run is NULL which seems wrong.

It's also strange that some of the values in msdb.dbo.sysjobschedules.next_run_date and msdb.dbo.sysjobschedules.next_run_time differ from the value stored in msdb.dbo.sysjobactivity.next_scheduled_run_date.

The sysjobschedules tables is saying the next run date is the occurrence after the value claimed as the next run date in the sysjobactivity table.

You'd think those would get calculated the same way and would be equal.

How do I correct this?

share|improve this question
2  
>> You'd think those would get calculated the same way and would be equal. Apparently not, take a look at the comment here: msdn.microsoft.com/en-us/library/ms188924.aspx – yogirk Feb 23 '12 at 18:32
right, sysjobschedules refreshes every 20 mins and won't necessarily have a value immediately, sysjobactivity is static, then? which one is correct? which one is actually telling me when the next job will fire? – towpse Feb 23 '12 at 19:05
I haven't tried it but consider using sysschedules table – yogirk Feb 23 '12 at 19:19
1  
okay, I googled around and found this, trying to answer questions for people is now my diabolical way of finding new stuff :) – yogirk Feb 23 '12 at 19:22
hey man thanks for the reply. i've seen that page as well and use the function that he's got outlined there. the fact of the matter is that the sysjobschedules & sysjobactivity tables don't agree as to when some jobs will fire which seems odd to me – towpse Feb 23 '12 at 19:41
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.