0

I am trying to obtain a list of ALL jobs (SQL Agent jobs). Specifically, I am trying to obtain the ID, name, status, LastRunOutcome, Last Run, Next Run, and most importantly description, owner AND schedule (eg. Every day at 5am) of each job.

I know that you can obtain all of this information through Job Activity Monitor window and then by clicking on each job. But I need to query this information.

My research here , here and here included a few articles that give partial info, but not complete one.

Any help in getting a full list of active and inactive jobs and their info is much appreciated. Thank you.

I have this code so far, which I hope is accurate:

SELECT
     job.job_id,
     notify_level_email,
     name,
     enabled,
     description,
     step_name,
     command,
     server,
     database_name
FROM
    msdb.dbo.sysjobs job
INNER JOIN 
    msdb.dbo.sysjobsteps steps        
ON
    job.job_id = steps.job_id
2

This gets you just about everything you mention and you can modify further to suit your needs:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
--AND msdb.dbo.sysjobservers.last_run_outcome = 0
  • Thank you for your answer. I apologize if I am missing something here but I am not able to get the most important information here: the occurrence schedule, for example: "occurs weekly at 5am". I looked into the freq_% fields there but they seem to be flags and not schedules. – LearnByReading Aug 28 '18 at 12:40

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.