I want to end up with a sort of Calendar that shows the different scheduled jobs and their steps (the exact command executed). But I"m having a hard time finding all the data. So my question is two-fold:
1) Does anyone know of a software (preferably open source or free) that can get this data from sql server 2000 in a spreadsheet or other easy to manipulate format?
2) Can anyone help me with the queries I'd need to get this data. How are SQL Agent Jobs organized in the database?
Any guidance on this issue would be welcome.
The information regarding jobs is stored in the MSDB database on your server.
Tables in the MSDB database will be of interest to you (names are self-explanatory)
The schedules are stored here
You can get execution history here
There are several built in procedures to help you with the details:
- sp_help_job
- sp_help_jobhistory
- sp_help_jobschedule
Using this query to get the info I need:
SELECT j.name, database_name,Step_id, Subsystem, command, Sjs.enabled, SJS.freq_type,
sjs.freq_interval, sjs.freq_subday_type, freq_subday_interval, freq_recurrence_factor, next_run_date, next_run_time
FROM sysjobsteps SJ
INNER JOIN sysjobs J ON SJ.Job_id = J.Job_Id
INNER JOIN sysJobschedules SJS ON J.Job_ID = SJS.Job_id
order by J.name, SJ.step_id
