How to Get All SQL Server Agent Jobs in SQL Server?

Introduction

SQL Server Agent is a crucial component of Microsoft SQL Server that allows database administrators to automate various tasks, including backup, maintenance, and data processing, through the use of jobs. These jobs can be scheduled to run at specific intervals, making it easier to manage and maintain your SQL Server environment. In this article, we will walk you through the process of retrieving a list of all SQL Server Agent jobs using SQL Server Management Studio (SSMS).

Retrieving SQL Server Agent Jobs

Follow these steps to retrieve a list of all SQL Server Agent jobs in SSMS:

Step 1. Connect to SQL Server

Launch SQL Server Management Studio and connect to your SQL Server instance.

Step 2. Open SQL Server Agent

In SSMS, navigate to the "Object Explorer" window on the left-hand side. Expand the server node to reveal the "SQL Server Agent" node. Right-click on "SQL Server Agent" and select "Jobs."

Open SQL Server Agent

Step 3. View Job List

Once you've selected "Jobs," the right-hand pane will display a list of all the SQL Server Agent jobs configured on the SQL Server instance. The list includes the following columns:

Job ID
Job Name
Owner
Enabled (whether the job is currently enabled or not)
Last Run Date
Next Run Date
Last Run Outcome

You can see an overview of the jobs, their statuses, and when they were last run, making it easier to manage your SQL Server Agent jobs.

Step 4. Job Details

To view more details about a specific job, you can right-click on the job name and select "Properties." This will open a new window where you can see a wealth of information related to the job, including its schedule, steps, and notifications.

Retrieving SQL Server Agent Jobs Using Query

The information about SQL Server Agent jobs is stored in the msdb database. We can use SQL queries to retrieve details about jobs, their steps, and other relevant information. The key tables we'll be working with are sysjobs and sysjobsteps. Below is a comprehensive SQL query that fetches various details about SQL Server Agent jobs.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName],
	case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
     CASE 
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime],
	  isnull (convert (varchar,d.Date_Created), 'None') CreatedDate
FROM 
    [msdb].[dbo].[sysjobs] AS [sJOB]
	
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id] 
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
		left outer join msdb.dbo.sysjobschedules e on e.job_id = [sJOB].job_id
		left outer join msdb.dbo.sysschedules d on e.schedule_id = d.schedule_id

The msdb.dbo.sysjobs table contains information about SQL Server Agent jobs.
The msdb.dbo.sysjobsteps table contains information about the steps within those jobs.
We use the inner join clause to link the two tables based on the job_id column.

Conclusion

Retrieving information about SQL Server Agent jobs is crucial for monitoring and managing automated tasks in a SQL Server environment. The provided SQL query allows you to obtain detailed information about jobs and their associated steps. By regularly querying and analyzing this information, database administrators can ensure the health and performance of their SQL Server instances. Additionally, this data can be used to troubleshoot job-related issues and optimize job schedules for better efficiency.

Thank You, and Stay Tuned for More