All geek questions in one place

SQL Agent Jobs How to Document

I want to end with a kind of Calendar that shows the different scheduled tasks and their actions (the exact command is being executed). But it’s hard for me to find all the data. So my question is double:

1) Does anyone know about software (preferably open source or free) that can receive this data from SQL Server 2000 in a spreadsheet or in another user-friendly format?

2) Can someone help me with the queries I need to get this data. How are SQL Agent Jobs organized in a database?

Any recommendations on this subject would be welcome.

+1
source share
2 answers

Job information is stored in the MSDB database on your server.

The tables in the MSDB database will be of interest to you (names are self-explanatory)

  • sysjobs
  • sysjobsteps

Schedules are stored here.

  • sysjobschedules

Here you can get the execution history.

  • sysjobhistory

There are several built-in procedures to help you in the details:

  • sp_help_job
  • sp_help_jobhistory
  • sp_help_jobschedule
+4
source

Using this query to obtain the necessary information:

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

0
source

Source: https://habr.com/ru/post/891753/

More articles:


All Articles