I know there is SELECT * FROM msdb..sysjobschedules, but that just shows the next time a job is scheduled to run.

I want to know how this is all determined and stored. So where does it store the value for Recurs every Monday check box?

migrated from stackoverflow.com Jul 17 '12 at 14:05

This question came from our site for professional and enthusiast programmers.

  • check out select * from msdb.dbo.sysschedules the system stored procedure sp_add_schedule updates or inserts into this table. – stb Jul 17 '12 at 14:03
  • I think that information is stored in msdb.dbo.sysschedules. Check out the msdn page. – Josien Jul 17 '12 at 14:04

Every 20 minutes (this seems to NOT be configurable), the 'next run' information in sysjobschedules is updated by looking at the information you can see in sysschedules.

You know how when you define a job you have to go through a slightly convoluted process of rather than just saying 'run this job every monday', instead defining a schedule that says that, and associating the job with the schedule? This is why - they have independent existences.


And if you want to link the info in sysschedules to your job info:

use msdb;

select SYSJ.name, SYSS.* from sysjobs as SYSJ
inner join sysjobschedules as SYSJS on SYSJ.job_id = SYSJS.job_id
inner join sysschedules SYSS on SYSS.schedule_id = SYSJS.schedule_id;

To elaborate on Josien's answer, a nice post tying all the Job-related sys entities together and giving you a nice set of queries to look at the info therein: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

I found it particularly helpful when I had a similar question and needed to build a report of all agent-jobs in our environment so that Dev's & other DBA's could look at them and provide context. Cheers!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.