sql/script to list jobs and schedules

Question

Post reply

sql/script to list jobs and schedules

timscronin

SSCertifiable

Points: 6851
More actions
April 26, 2010 at 10:28 am

#139901

I had a nice script for sql 2000 which would show all sql agent jobs, steps and schedules. Of course under 2005 won't work. I know the 3 tables below
select * from dbo.sysjobs
select * from sysjobsteps
select * from sysjobschedules
It is an easy link, where I am lost is when linking the jobs to the schedules is how to drive and when and how often?

Viewing 10 posts - 1 through 9 (of 9 total)

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

durai nagarajan SSCoach Points: 15775 More actions · Answer 1

Joc activity monitor will giev you all jobs with last run date and future run date , will you need more.

Regards
Durai Nagarajan

timscronin SSCertifiable Points: 6851 More actions · Answer 2

Yes, I am looking for a schdule in a readable format which shows when each job is scheduled. I use to manipulate sysjobscheduled which is gone now

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

This is right in MSDB: dbo.sysjobschedules

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

timscronin SSCertifiable Points: 6851 More actions · Answer 4

Yes it is there but they took out all the columns to manipulate, only 4 colums

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

I guess I'm confused, the job_id is right there along with the next run datetime. What else are you looking for?

If you need a literal breakdown of the schedule, try looking at sysschedules.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

timscronin SSCertifiable Points: 6851 More actions · Answer 6

timscronin

SSCertifiable

Points: 6851

April 26, 2010 at 1:04 pm

#1157822

Thanks that was the missing table I needed

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 7

Cool. Glad I could help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Marek Grzymala SSCommitted Points: 1946 More actions · Answer 8

If you need a literal breakdown of the schedule, try looking at sysschedules.

Except that in SQL 2000 there is no sysschedules table. What worked for me was this: http://solihinho.wordpress.com/2009/01/01/query-for-listing-sql-server-job-schedule/[/url]

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 9

Marek Grzymala (12/30/2013)
If you need a literal breakdown of the schedule, try looking at sysschedules.
Except that in SQL 2000 there is no sysschedules table. What worked for me was this: http://solihinho.wordpress.com/2009/01/01/query-for-listing-sql-server-job-schedule/[/url]

Except this was specifically a question for 2005. Look at the original question. The OP had a script for 2000.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning