Querying SQL Server Agent Job Information

By:   |   Updated: 2011-12-09   |   Comments (81)   |   Related: 1 | 2 | 3 | > SQL Server Agent


Problem

I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.

Solution

It is a common scenario in IT the industry where people move from a dev/test role to a DBA role and vice-versa due to various reasons. It is very essential for the person to be able to adapt easily when he/she is moved to a different role. SQL Server is known for its usability and is one of the most liked tools in the community because of its user friendliness. In this tip, we will see how to query some basic job monitoring and job setup information.  The intention of this tip is to act as a good starting point for people who are new to the DBA role and for those who need to monitor the SQL Server Agent Jobs.

Before we get into querying the required information, let's get a basic understanding of msdb database. msdb is a SQL Server System Database and is used to store the Configuration, Processing, & Metadata information related to the SQL Server features, including:

In this tip we will retrieve the following information related to SQL Server Agent Jobs by querying some of the system tables in the msdb database:

  • Job Setup/Configuration Information
  • Job Execution Information
  • Job Step(s) Setup/Configuration Information
  • Job Step(s) Execution Information
  • Schedule Information

SQL Server Agent Job Setup and Configuration Information

SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQL queries. Irrespective of the approach used, this information is stored in a set of SQL Server System Tables present in the msdb database.

The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]

The following is a brief description of each of the fields returned from the above query:

  • [JobID]: A unique identifier for the SQL Server Agent job (GUID).
  • [JobName]: Name of the SQL Server Agent job.
  • [JobOwner]: Owner of the job.
  • [JobCategory]: Category to which the job belongs like Replication Snapshot, Database Maintenance, Log Shipping, etc.
  • [JobDescription]: Description of the job.
  • [IsEnabled]: Indicator representing whether the job is enabled or disabled.
  • [JobCreatedOn]: Date and time when the job was created.
  • [JobLastModifiedOn]: Date and time when the job was last modified.
  • [OriginatingServerName]: Server from which the job executed.
  • [JobStartStepNo]: Step number from which the job is set to start. SQL Server allows us to have multiple steps within a job and the job can be set to start from whichever step the user wants it to start from.
  • [JobStartStepName]: Name of the step from which the job is set to start.
  • [IsScheduled]: Indicator representing whether the job is scheduled or not. The jobs can be either scheduled to run on specified day(s) at a specified time or can be invoked through code like T-SQL, etc.
  • [JobScheduleID]: Unique identifier of the schedule associated with the job (GUID).
  • [JobScheduleName]: Name of the schedule associated with the job. SQL Server allows us to associate multiple schedules with one job, in which case, the above query would return one row for each schedule associated with each job.
  • [JobDeletionCriterion]: The criterion for deleting the job. SQL Server Agent has a feature which allows us to delete/drop the job based on a certain criterion so that there is no need to manually delete/cleanup the jobs.

SQL Server Agent Job Execution Information

SQL Server Agent stores the history of job execution in system tables in msdb database.

The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , 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 (HH:MM:SS)]
    , [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]
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
ORDER BY [JobName]

The following is a brief description of each of the fields returned from the above query:

  • [JobID]: A unique identifier for the SQL Server Agent job (GUID) for which the execution history is being reported.
  • [JobName]: Name of the SQL Server Agent job.
  • [LastRunDateTime]: Date and time when the job was run last time (corresponds to the most recent run).
  • [LastRunStatus]: Status or outcome of the last job run.
  • [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
  • [LastRunStatusMessage]: More descriptive message about the job status/outcome.
  • [NextRunDateTime]: Date and time when the job will run next time. This information is available only for the jobs which are scheduled (a schedule is associated with a job).

SQL Server Agent Job Steps Setup and Configuration Information

In SQL Server Agent, a job is the parent level entity, which contains one or more steps. A step is the child/low level entity, which contains the actual commands/instructions for performing a specific task. Whereas a job defines the sequence of execution of steps, schedule for running steps, etc.

The following query gives us the Job Step level Setup and Configuration information, which can also be found in the Job Step Properties window in SSMS.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' 
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 
                    + ' ' 
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [JobName], [StepNo]

The following is a brief description of each of the fields returned from the above query:

  • [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
  • [JobName]: Name of the SQL Server Agent job.
  • [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
  • [StepNo]: Sequence number of the step representing the position of the step in the job.
  • [StepName]: Name of the SQL Server Agent Job Step.
  • [StepType]: Subsystem/Type of the Job Step like SQL Server Integration Services Package, Transact-SQL Script (T-SQL), ActiveX Script etc.
  • [RunAs]: Account under which the job step should be run/executed. This will contain a value in the above query output only when run through a proxy.
  • [Database]: Name of the database in which the command is executed. This applies only when the Step Type is Transact-SQL Script (T-SQL).
  • [ExecutableCommand]: The actual command which will be executed by the subsystem.
  • [OnSuccessAction]: Action to be taken by SQL Server Agent when the job step succeeds.
  • [RetryAttempts]: Number of retry attempts made by SQL Server Agent in case the job step fails.
  • [RetryInterval (Minutes)]: Time interval in minutes between each retry attempt in case the job step fails and SQL Server Agent tries to re-run it.
  • [OnFailureAction]: Action to be taken by SQL Server Agent when the job step fails.

SQL Server Agent Job Steps Execution Information

SQL Server Agent stores the history of the execution of each of the job steps in system tables in msdb database.

The following query gives us the details of last/latest execution of the job step. This information can also be found in the Job History/Log File Viewer windows in SSMS.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[last_run_outcome]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 5 THEN 'Unknown'
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
      AS [LastRunDuration (HH:MM:SS)]
    , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
    , CASE [sJSTP].[last_run_date]
        WHEN 0 THEN NULL
        ELSE 
            CAST(
                CAST([sJSTP].[last_run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
ORDER BY [JobName], [StepNo]

The following is a brief description of each of the fields returned from the above query:

  • [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
  • [JobName]: Name of the SQL Server Agent job.
  • [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
  • [StepNo]: Sequence number of the step representing the position of the step in the job.
  • [StepName]: Name of the SQL Server Agent Job Step.
  • [LastRunStatus]: Status or Outcome of the step execution when the job/step executed last time.
  • [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
  • [LastRunRetryAttempts]: Number of retry attempts made by SQL Server Agent during the last run of the job step.
  • [LastRunDateTime]: Date and time when the job step was last run.

SQL Server Agent Job Schedule Information

SQL Server allows creating schedules for performing various tasks at a specified date and time. This can be a one time schedule or a recurring schedule with or without an end date. Each schedule can be associated with one or more SQL Server Agent Jobs.

The following query gives us the list of schedules created/available in SQL Server and the details (Occurrence, Recurrence, Frequency, etc.) of each of the schedules.

SELECT 
    [schedule_uid] AS [ScheduleID]
    , [name] AS [ScheduleName]
    , CASE [enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

The following is a brief description of each of the fields returned from the above query:

  • [ScheduleID]: Unique identifier of the schedule (GUID).
  • [ScheduleName]: Name of the schedule. SQL Server allows one schedule to be associated with more than one job.
  • [IsEnabled]: Indicator representing whether a schedule is enabled or disabled.
  • [ScheduleType]: The type of the schedule.
  • [Occurrence]: Occurrence of the schedule like Daily, Weekly, Monthly, etc.
  • [Recurrence]: Recurrence of the schedule like specific day(s), Specific Days of the Week, Number of weeks, etc.
  • [Frequency]: How frequently the job should run on the day(s) when it is scheduled to run such as: Occurs only once on the scheduled day(s), Occurs every 2 hours on the scheduled day(s) etc. between specified start and end times.
  • [ScheduleUsageStartDate]: Effective start date from when the schedule will be used.
  • [ScheduleUsageEndDate]: Effective end date after which the schedule will not be used.
  • [ScheduleCreatedOn]: Date and time when the schedule was created.
  • [ScheduleLastModifiedOn]: Date and time when the schedule was last modified.
Next Steps
  • Review the scripts in this tip and start extending them to meet your monitoring and documentation requirements.
  • Write scripts to get the following information:
    • Get the list of long running jobs
    • Get the list of jobs scheduled to run in next 24/48 hours
    • Get the list of jobs failed in last 24/48 hours
  • Check out other SQL Server Agent related tips
  • Check out these additional resources:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-12-09

Comments For This Article




Tuesday, November 15, 2022 - 5:08:06 PM - Kael Dowdy Back To Top (90688)
This is an older article, I know, but it's been EXTREMELY helpful as I work towards querying details out of the SQL Agent Job system tables!

I did want to point out a bug I see in the 2nd query, we have a MIN() over both the next_run_date and next_run_time fields, and those values are being bubbled up from the subquery into the main query. This part of the query is meant to show the next time the job is scheduled to be run.

However, if u have multiple schedules configured for the same job, doing a MIN() over each field independent of one another yields incorrect results. The alternative would be to CAST the next_run_date & next run_time into a date/time field within the subquery, and THEN do a MIN over that. Otherwise, with multiple schedules, you may capture the run_date from one record and the run_time from another record.

See the example on this screenshot: https://imgur.com/a/u6d7dAd

Hope this helps others!

Friday, February 5, 2021 - 2:44:54 PM - Leo Gurdian Back To Top (88188)
Thank you for saving me a lot of time Dattatrey. Here's a compiled version taking bits and pieces of your different results:

/*
PURPOSE: Querying SQL Server Agent Job Information
AUTHOR: Dattatrey Sindol
Source: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
CREATED: 2011-12-09
HISTORY:

02.03.2021 L.Gurdian added JobOwner
02.01.2021 Corey Bui added actual schedule
02.01.2021 L.Gurdian Added 1.) The purpose
2.) The schedule for the job
3.) If it’s scheduled or not
4.) Originating server
*/
SELECT sJOB.name AS JobName
, sJOB.description AS JobDescription
, sJSTP.step_id AS StepNo
, sJSTP.step_name AS StepName
, CASE sJSTP.subsystem
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS StepType
, sDBP.[name] AS [JobOwner]
, sPROX.name AS RunAs
, sSVR.name AS OriginatingServerName
, sJSTP.database_name AS DatabaseName
, CASE
WHEN sSCH.schedule_uid IS NULL THEN 'No'
ELSE 'Yes'
END AS IsScheduled
, CASE
WHEN sSCH.enabled = 1 THEN 'Yes'
ELSE 'No'
END AS ScheduleEnabled
, sSCH.name AS JobScheduleName
, case
when sSCH.freq_type = 1
then 'Once, ' + ' starting at ' + cast(sSCH.active_start_date as varchar(100))
+stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when sSCH.freq_type = 4
then 'Daily, ' +
'every ' + cast (sSCH.freq_interval as varchar(3)) + ' day(s), ' +
case
when sSCH.freq_subday_type = 2 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when sSCH.freq_subday_type = 4 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when sSCH.freq_subday_type = 8 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end
when sSCH.freq_type = 8
then 'Weekly, ' +
replace
(
CASE WHEN sSCH.freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+CASE WHEN sSCH.freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
,', '
,''
) + ', ' +
case
when sSCH.freq_subday_type = 2 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when sSCH.freq_subday_type = 4 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when sSCH.freq_subday_type = 8 then ' every ' + cast(sSCH.freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(sSCH.active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end
when sSCH.freq_type = 16
then 'Monthly, ' +
'on day ' + cast (sSCH.freq_interval as varchar(3))
when sSCH.freq_type = 32
then 'Monthly, ' +
'on week ' +
cast(sSCH.freq_relative_interval as varchar(100)) + ', ' +
case sSCH.freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Weekend day'
end +
' every ' + cast(sSCH.freq_recurrence_factor as varchar(100)) + ' months'
when sSCH.freq_type = 64
then 'Auto starts when SQL Agent starts up'
else
''
end ActualSchedule
, sJSTP.command AS ExecutableCommand
, CASE sJSTP.on_success_action
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST(sJSTP.on_success_step_id AS VARCHAR(3)))
+ ' '
+ sOSSTP.step_name
END AS OnSuccessAction
, sJSTP.retry_attempts AS RetryAttempts
, sJSTP.retry_interval AS RetryIntervalInMinutes
, CASE sJSTP.on_fail_action
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST(sJSTP.on_fail_step_id AS VARCHAR(3)))
+ ' '
+ sOFSTP.step_name
END AS OnFailureAction
, CASE [sJSTP].[last_run_date]
WHEN 0 THEN NULL
ELSE
CAST(
CAST([sJSTP].[last_run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, CASE [sJSTP].[last_run_outcome]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 5 THEN 'Unknown'
END AS [LastRunStatus]
FROM msdb.dbo.sysjobsteps AS sJSTP
JOIN msdb.dbo.sysjobs AS sJOB ON sJSTP.job_id = sJOB.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS sOSSTP ON sJSTP.job_id = sOSSTP.job_id
AND sJSTP.on_success_step_id = sOSSTP.step_id
LEFT JOIN msdb.dbo.sysjobsteps AS sOFSTP ON sJSTP.job_id = sOFSTP.job_id
AND sJSTP.on_fail_step_id = sOFSTP.step_id
LEFT JOIN msdb.dbo.sysproxies AS sPROX ON sJSTP.proxy_id = sPROX.proxy_id
LEFT JOIN msdb.sys.servers AS sSVR ON sJOB.originating_server_id = sSVR.server_id
LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH ON sJOB.job_id = sJOBSCH.job_id
LEFT JOIN msdb.dbo.sysschedules AS sSCH ON sJOBSCH.schedule_id = sSCH.schedule_id
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
WHERE sJOB.enabled = 1
ORDER BY sJOB.name
, sJSTP.step_id

Thursday, November 12, 2020 - 10:56:32 AM - Sandeep Kataria Back To Top (87790)
Great post Datta! thank you. it helped me. I have 10's (big under estimation) of jobs in the many SQL server my team manages and I needed a comprehensive report on how things are, what the configurations are etc. (especially clashing timings) Nice work.

Wednesday, June 24, 2020 - 1:34:38 AM - Kourosh Back To Top (86035)

Greate thanks. I also need to get a backup from my jobs using C#. In order to do that I have to execute a specific query. I can get the list of all my sql agent jobs and their info but I don't know to execute what query to get a backup from my jobs. More specifically I want to execute a query that performs the "script job as" operation which can be done using the GUI of the sql server management studio.

Tuesday, March 10, 2020 - 10:10:11 AM - Turino Sastrawijaya Back To Top (85039)

Amazing Post...!, Thank you, very helpful

Tuesday, October 22, 2019 - 8:27:59 AM - Joe Gavin Back To Top (82859)

Very handy Datta. Thank you.

Monday, November 5, 2018 - 5:57:30 PM - ClaireM Back To Top (78163)

Amazing script!! very good work...

Wednesday, May 30, 2018 - 4:31:36 AM - Michael Back To Top (76050)

 Looking for a SIMPLE script/solution that gives me a column named "Estimated_Time_left" for running server agent jobs.

DMVs doesnt seem to support this (or me beeing wrong) so a simple solution please.

 

Friday, April 13, 2018 - 9:03:10 AM - kyle Jardine Back To Top (75695)

 Thank you 

Very Helpful

 

Wednesday, March 7, 2018 - 2:12:10 PM - Niharika Back To Top (75365)

 

 

What if I need to get the list of parameters used in a jab?

Friday, January 5, 2018 - 7:11:21 AM - Navneet Back To Top (74816)

Hi,

The above information is good enough,

Please Help i am new in the SQL:

> Find the USER which has Intentionally or Accidently Disabled, the Any enabled job.

> Last modified action by the USER

 

 

Monday, November 27, 2017 - 11:39:56 AM - Darren B Back To Top (73292)

Fantastic acrticle.

Very useful SQL.

You could lose all the unneeded square brackets (personal annoyance only, technically safest approach but still unneeded).

Wednesday, October 18, 2017 - 9:06:49 AM - NAKIL Back To Top (68506)

Hi Datta 

I am new to SQL server and SSIS.

I need to create a SQL query to fetch all Jobs and its related packages and their start time and finish time.

Basically I m trying to see a trend how much does each package takes daily in any particular Job.

Can this be achived? Is there relation between the Job table and Package table?

Thanks

Thursday, August 3, 2017 - 2:31:48 AM - Shamas Bahrain Back To Top (63827)

It helped a lot to get all information regarding Job, configuration, steps detail and execution.  Now I can generate report to get daily job detail in my mailbox. Great post.

 

Saturday, April 15, 2017 - 11:52:41 PM - Mukul Back To Top (54949)

One of the best blog post i've seen related to agent jobs information. Thanks.

Friday, February 24, 2017 - 12:43:16 PM - Jason Hurley Back To Top (46646)

Wow, this is extremely helpful to have all of this in one place.  And the queries are so well written and easy to read!  Thanks Datta!!

 

Tuesday, December 13, 2016 - 5:28:54 PM - Miguel A. Chavez Back To Top (44963)

 Thank you for your post . Very helpful

 

Wednesday, September 21, 2016 - 1:30:23 AM - Debaditya Back To Top (43373)

 While querying the NextRunDateTime from msdb.dbo.sysjobschedules, have you not encountered any discreepancy in the actual next scheduled run datetime and the value in this table.

I have noticed this table is not udpated in real-time and tested this theory with a job which is scheduled to run every 10 minutes. While the data in the job activity monitor shows the correct next scheduled run datetime (hence it can trigger the job at the right time), the data in this table is late to update.

Have you encountered this scenario?

 

Friday, April 1, 2016 - 10:22:52 AM - mendl Back To Top (41119)

 

 Thank you. These are very useful!

Sunday, February 21, 2016 - 11:53:31 PM - Sean Back To Top (40740)
Awesome stuff. Very helpful. Thank you very much!

Thursday, January 28, 2016 - 10:23:35 AM - BrainDonor Back To Top (40530)

A useful set of scripts, thank you.

 

Thursday, August 27, 2015 - 10:05:45 PM - Kundan Raj Back To Top (38555)

Hi Datta,

 

can you help me on this.

our requirement is to get all the job and their history on all instances and log them in a centralized server.

this process should run through a SSIS package.

Can you suggest if you have a plan \ template for this.

Thanks,

Raj.

 

 

Thursday, July 2, 2015 - 9:16:28 PM - John Back To Top (38122)

Hi Datta,

I will a few other commenters I am an accidental DBA.

These queries are brilliant and incredibly useful and have really helped me.

Much appreciated!!!

John

Tuesday, June 9, 2015 - 3:48:47 PM - Vaibhav Back To Top (37875)

Hello Datta,

This is an immensely useful piece of code. I happen to be an accidental DBA and fortunately I ran into your blog. Thanks a lot for this HUGE help in my initial steps of taking up DBA role in addition to my current DB developer.

Regards,

Vaibhav

 

Friday, April 17, 2015 - 5:46:47 AM - Jagadish Back To Top (36962)

Hi Datta Sir,

 

I am working on SSRS rendering from SSIS. I have completed with parameters and without parameters. And Chart report also rendering without any issue. Chart means only generating top of RDMS chart report rendering. But Top of Cube generated reports is not rendering. Could you please help me. If you have any code please send to my email.

One more thing I am passing parameters through database columns. For Eg.., ParamName1, ParamValue1, ParamName2,ParamValue2....,

If I will give single value working Eg.., ParamName1=Company, ParamValue1=100, ParamName2=StartDate, ParamValue2=01/01/2014.

Like this report rendering without any issue. But when I am giving like  ParamName1=Company, ParamValue1=(100,105)     , ParamName2=StartDate, ParamValue2=01/01/2014. Multi value parameter of ParamValue1 that time giving error on Setexecution.

 

Please help me these two scenarios.

 

Regards,

Jag

Monday, March 9, 2015 - 12:18:04 PM - Hank Freeman Back To Top (36472)

Very Nice ! Don't know how long it took you to provide all of this code, but you have helped a ton of DBA(s).

So than you very much.

Thursday, January 15, 2015 - 6:32:02 AM - Younus Back To Top (35952)

Hi Guys,

Could you please provide me with a query which results

Select JobName,SSIS_Package_Name Tbl_Name???

Thanks

Thursday, November 20, 2014 - 5:53:55 AM - Jase Back To Top (35348)

Fantastic post Dattarey, thank you.

Tuesday, October 7, 2014 - 4:38:31 AM - Bikram Pattanayak Back To Top (34857)

HI,

 

Could you please help me generating this report in the below format from Sql Script. I have tried for a long time but unable to acheive the Goal.

Please help.

 

I have a 5 jobs running in my system and I want to create a report daily in excel file like the below

 

JOB A
 
JOB B
 
JOB C
 
START TIME
 
END TIME
 
DIFFERENCE
 
START TIME
 
END TIME
 
DIFFERENCE
 
START TIME
 
END TIME
 
DIFFERENCE
                 
                 
                 

Saturday, June 21, 2014 - 12:18:43 AM - Luis Fernando Back To Top (32333)

Excellent Post!!! Thanks 

Monday, April 28, 2014 - 3:57:00 PM - KillerDBA Back To Top (30536)

 

Hi Datta,

 

      I recently had a database set to (restricted user) mode and was never set back to multiuser mode. Now,  I know that happens because of a maintenance job that would require for a database to that mode so it can perform its maintenance task and then set it back to multiuser mode.

Now what I need is to figure out is that:-

 

1. I have a spid that shows me what time the database was set to restricted users mode from the sqlServer Log File viewer and also the spid number, how can I connect that spid to a user??

2. Can I check for any recently created and deleted Jobs between certain dates??

I suspect someone recently created a job and ran it then deleted it after causing the havoc.

 

Sunday, April 6, 2014 - 3:15:34 PM - Dattatrey Sindol (Datta) Back To Top (29983)

Hi MSSQL DBA,

Please take a look at this thread: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d7f70ef8-ff2b-489a-a5c5-8a93a2d8b6fe/how-to-recover-job-history?forum=sqldatabaseengine.

Hope it helps!

Best Regards,

Dattatrey Sindol (Datta)

Wednesday, April 2, 2014 - 12:11:31 AM - MSSQL DBA Back To Top (29937)

hi,

I want deleted job log of sql agent ??

 

Regards,

DBA

Friday, March 14, 2014 - 2:28:54 AM - RehanPattni Back To Top (29750)

Thanks for sharing this scripts.I have used ur last script to get job frequency.I found that it giving output of all jobs like system jobs and i have also found that it shows all jobs as enable even if they are disable.

Wednesday, February 26, 2014 - 11:45:01 AM - Alan Whitten Back To Top (29592)

I am really digging the "SQL Server Agent Job Execution Information" code.   I am making a Gantt chart in Excel and this provides the source information quite well - EXCEPT  


I have a few jobs that are returning NULL values for last Run time, date, status - even tho the jobs ran sucessfully.   If I remove the LEFT from the 2 joins, I don't see them, but then I am missing data.   Thoughts?

Monday, January 27, 2014 - 4:09:01 PM - sridar Back To Top (29240)

Hello,

 

I have an issue where one of my job is failing from last 4 days but when I looked into job history I could not see anything logged.

Have tried from backed tables as well.

 

Please suggest me if there is any way by which I can track the reason why the job is failing.

Friday, January 10, 2014 - 3:18:58 PM - terry sprague Back To Top (28016)

Great scripts, really helped me out. Thanks!

Tuesday, November 12, 2013 - 8:58:36 PM - Dattatrey Sindol Back To Top (27476)

Hi Mark,

You can combine the "SQL Server Agent Job Execution Information" and "SQL Server Agent Job Steps Execution Information" queries and get the last execution entry for a job step if it has failed, else return empty string/default values.

Best Regards,

Dattatrey Sindol (Datta)

Monday, November 11, 2013 - 10:46:31 AM - Mark Freeman Back To Top (27460)

One issue I noted with the Execution Information query is that it shows 'Succeeded' for the [LastRunStatus] even if a step in the job has an issue but the job itself succeeded.

For example, I have a job with two steps. For one execution of this job, Log File Viewer shows a warning icon next to this execution. When I expand the job, it shows Step ID 2 with a succes icon and then Step 1 with an error icon. 

If I look at sysjobhistory for this run_date and run_time, I get two records, one for step_id 0 that has a run_status of 1 (Succeeded) and one for step_id 1 that has a run_status of 0 (Failed) and a non-zero sql_message_id.

How can the Execution Information query be enhanced to indicate that there was a step that did not succeed, even though the job itself succceeded, such as in this case? I would want to see the message for the failed step in the report, not the message telling me that the job succeeded.

Tuesday, November 5, 2013 - 3:05:09 PM - Patricia Beyer Back To Top (27404)

I need a query to retrieve all snapshots that did not update for a specific time period.

Wednesday, July 31, 2013 - 1:04:38 PM - Joanna Back To Top (26076)

Great scripts. THANK YOU VERY MUCH!

Friday, June 21, 2013 - 1:00:49 PM - Joey Back To Top (25520)

Datta,

     Thank you for the great information and queries!  Your writing, subject coverage, and overall article flow was very impressive!  Thank you!  

Wednesday, June 5, 2013 - 11:56:02 AM - RyanWooster Back To Top (25311)

For those still using server 2000 i also had this problem of them not working so i kind of fudged them.

I have only recienly graduated so excuse if code is a bit off and suggestions for improvement are welcome but these return useful data if your company still has 2000 servers like mine

SELECT
    [sJOB].[job_id] AS [JobID]
     ,[sJOB].[name] AS [JobName]
    , 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 (HH:MM:SS)]
    , [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] INTO #tbl_Output     
     
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MAX([next_run_date]) AS [NextRunDate]
                    , MAX([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
ORDER BY [JobName]



SELECT
       O.[JobName]
      ,O.[LastRunDateTime]
      ,O.[NextRunDateTime]
      ,O.[LastRunDuration (HH:MM:SS)]
      ,O.[LastRunStatusMessage]     
      ,O.[LastRunStatus]
  FROM [dbo].[#tbl_Output] AS O
 
  INNER JOIN (SELECT
        JobName,
        MAX([LastRunDateTime]) AS [LastRunDateTime],
        MAX([NextRunDateTime]) AS [NextRunDateTime]
    FROM [dbo].[#tbl_Output]
GROUP BY JobName) AS D ON O.[JobName] = D.[JobName] AND O.[LastRunDateTime] = D.[LastRunDateTime]
 
 
 DROP TABLE #tbl_Output

===========================================================================
 

SELECT
    [schedule_id] AS [ScheduleID]
    , [name] AS [ScheduleName]
    , CASE [enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' '
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at '
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    --, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysjobschedules]
ORDER BY [ScheduleName]

Wednesday, March 13, 2013 - 9:57:20 PM - Dattatrey Sindol Back To Top (22788)

Hi John,

 

I am glad you found the code helpful.

To keep the things simple, I would suggest using the below syntax.

 

SELECT *

FROM 

(

<Entire Query under "SQL Server Agent Job Execution Information" heading (Without ORDER BY Clause)>

) AS T

WHERE [NextRunDateTime] > GETDATE()

GO

 

Hope that helps!

 

Best Regards,

Dattatrey Sindol (Datta)

Monday, March 11, 2013 - 5:22:43 PM - John Waclawski Back To Top (22714)

Thank you very much for this code!  It got me started in the right direction.  I do have a question though.

 

Is there a way to modify this code to only show jobs running in the future?  I've tried the following as a WHERE statement

Where

Cast(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) AS datetime) >= GETDATE()

 

and

 

Where

Cast(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) AS smalldatetime) >= GETDATE()

 

In both cases I get the error "Conversion failed when converting date and/or time from character string."

 

Thank you again!!

 

Tuesday, February 5, 2013 - 3:08:06 PM - Ritesh Sheth Back To Top (21937)

There is a easier way to get the job detail.

If is SQL server 2000

select * from msdb.dbo.sysjobs

IF is SQL server 2005

select * from msdb.dbo.sysjobs_view

Friday, February 1, 2013 - 12:05:42 PM - Jacque Back To Top (21865)

Dattatrey - you are a life saver.

Thanks! 

Thursday, January 31, 2013 - 12:15:35 PM - fairy Back To Top (21848)

Hello Datta

Thanks for your reply. I tried so many things but nothing work out for this. I tried with the above solution it gives me 300 as output but I dont know how to apply this to those particular columns. The date and time are string values .Here in the above solution we have converted it into a particular format. Also, the date and time columns and diffrent how can i apply this to the query. We are also using '&' operator for this. I am confused about the solution yuou have given.

Please help.

 

Regrads,

Fairy

 

Thursday, January 31, 2013 - 9:57:26 AM - Dattatrey Sindol Back To Top (21841)

Hi Fairy,

 

First get the offset of your timezone in minutes using a query as shown below:

SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE())

Then use the output of this and add/subtract the appropriate number of minutes to/from each of the dates derived above.

 

Hope that helps!

 

Best Regards,

Dattatrey Sindol (Datta)

Wednesday, January 30, 2013 - 11:58:47 AM - fairy Back To Top (21798)

 END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at '
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every '
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & '
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

 

 

In the above code how can I convert this local datetime to GMT or UTC datetime. please help me

 

 

 

 

 

 

 

 

 


 

Tuesday, January 29, 2013 - 1:31:28 PM - Ludwig Guevara Back To Top (21775)

Hi chaithanya,

Please, see the following tables at the server with Log Shipping, database MSDB

-- Stores alert job ID.
SELECT * FROM log_shipping_monitor_alert

-- Stores error details for log shipping jobs. You can query this table see the errors for an agent session. Optionally, you can sort the errors by the date and time at which each was logged. Each error is logged as a sequence of exceptions, and multiple errors (sequences) can per agent session.
SELECT * FROM log_shipping_monitor_error_detail

-- Contains history details for log shipping agents. You can query this table to see the history detail for an agent session.   
SELECT * FROM log_shipping_monitor_history_detail

-- Stores one monitor record for the primary database in each log shipping configuration, including information about the last backup file and last restored file that is useful for monitoring.
SELECT * FROM log_shipping_monitor_primary

-- Stores one monitor record for each secondary database, including information about the last backup file and last restored file that is useful for monitoring.
SELECT * FROM log_shipping_monitor_secondary

For more information:

http://msdn.microsoft.com/en-us/library/ms190224.aspx

Wednesday, January 23, 2013 - 6:47:26 AM - Sarah Hampson Back To Top (21643)

 

Hi

I have the same problem as sayeedpilot (posted above: Thursday, November 29, 2012 - 3:42:40 AM)  

The scripts do not run on SQL2000, I get the same issue:Invalid object name 'msdb.dbo.sysproxies'.

Can anyone provide a script that does the same thing but on SQL2000.

Thank you so much.

Monday, January 14, 2013 - 8:07:06 PM - chaithanya Back To Top (21445)

Hi Dattatrey Sindol,

Thankkyou very much. 

Its helped me alot.


Thanks

Monday, January 14, 2013 - 7:59:52 PM - chaithanya Back To Top (21444)

HI Ludwig Guevara,

Could you please explain the variables that are to be declared and Is this query helps to monitor logshipping restoring jobs.

Could you please suggest me SP for logshipping i.e getting email alert when logshipping fails.


Thanks

Monday, January 14, 2013 - 1:04:44 PM - Ludwig Guevara Back To Top (21439)

Hi,

Is not mine, but...

Reports details of the schedule information for all scheduled jobs on the server.
---------------------------------------------------------------------------------------------------
-- Date Created: September 21, 2006
-- Author:       William McEvoy
-- Description: 
---------------------------------------------------------------------------------------------------

select  --'Server'       = left(@@ServerName,20),
       'JobName'      = left(S.name,128),
       'ScheduleName' = left(ss.name,50),
       'Enabled'      = CASE (S.enabled) WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE '??' END,
       'Frequency'    = CASE(ss.freq_type)
                          WHEN 1  THEN 'Once'
                          WHEN 4  THEN 'Daily'
                          WHEN 8  THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                          WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                          WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
                          WHEN 64 THEN 'SQL Startup'
                          WHEN 128 THEN 'SQL Idle'
                          ELSE '??'
                        END,
       'Interval'    = CASE
                         WHEN (freq_type = 1)                       then 'One time only'
                         WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                         WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                         WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
                                                       from (select ss.schedule_id,
                                                                     freq_interval,
                                                                     'D1' = CASE WHEN (freq_interval & 1  <> 0) then 'Sun ' ELSE '' END,
                                                                     'D2' = CASE WHEN (freq_interval & 2  <> 0) then 'Mon '  ELSE '' END,
                                                                     'D3' = CASE WHEN (freq_interval & 4  <> 0) then 'Tue '  ELSE '' END,
                                                                     'D4' = CASE WHEN (freq_interval & 8  <> 0) then 'Wed '  ELSE '' END,
                                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                                                                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                                                                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                                                 from msdb..sysschedules ss
                                                                where freq_type = 8
                                                           ) as F
                                                       where schedule_id = sj.schedule_id
                                                    )
                         WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
                         WHEN (freq_type = 32) then (select freq_rel + WDAY
                                                        from (select ss.schedule_id,
                                                                     'freq_rel' = CASE(freq_relative_interval)
                                                                                    WHEN 1 then 'First'
                                                                                    WHEN 2 then 'Second'
                                                                                    WHEN 4 then 'Third'
                                                                                    WHEN 8 then 'Fourth'
                                                                                    WHEN 16 then 'Last'
                                                                                    ELSE '??'
                                                                                  END,
                                                                    'WDAY'     = CASE (freq_interval)
                                                                                    WHEN 1 then ' Sun'
                                                                                    WHEN 2 then ' Mon'
                                                                                    WHEN 3 then ' Tue'
                                                                                    WHEN 4 then ' Wed'
                                                                                    WHEN 5 then ' Thu'
                                                                                    WHEN 6 then ' Fri'
                                                                                    WHEN 7 then ' Sat'
                                                                                    WHEN 8 then ' Day'
                                                                                    WHEN 9 then ' Weekday'
                                                                                    WHEN 10 then ' Weekend'
                                                                                    ELSE '??'
                                                                                  END
                                                                from msdb..sysschedules ss
                                                                where ss.freq_type = 32
                                                             ) as WS
                                                       where WS.schedule_id =ss.schedule_id
                                                       )
                        WHEN (freq_type = 64) then 'Runs when the SQL Server Agent service starts'
                        WHEN (freq_type = 128) then 'Runs when the SQL Server Agent service starts'
                       END,
       'Time' = CASE (freq_subday_type)
                        WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                        WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                        WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                        WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                        ELSE '??'
                      END,

       'Next Run Time' = CASE SJ.next_run_date
                           WHEN 0 THEN cast('??' as char(10))
                           ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
                         END
 
   from msdb.dbo.sysjobschedules SJ
   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id
   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id
    where s.enabled = @enabled
order by S.name

GO

Friday, January 11, 2013 - 8:09:43 AM - Dattatrey Sindol Back To Top (21400)

Hi Chaithanya

 

You can make use of the query in "SQL Server Agent Job Execution Information" section. For your scenario, please add the following two filters in WHERE Clause:

 

WHERE

[sJOB].[name] LIKE %LSrestore%

AND [sJOBH].[run_status] = 0 -- Failed

 

Hope that helps.

 

Best Regards,

Dattatrey Sindol (Datta)

Wednesday, January 9, 2013 - 2:07:08 PM - chaithanya Back To Top (21353)

Thank you very much for the effort.

Could you please suggest me SCRIPT for only failed RESTORING jobs(jobname is like %LSrestore%).so that it helps me to monitor Logshipping status in secondary server.

I need to setup email alert when the restoring job fails in secondary server.we have more than 300 DBs in logshipping.

 

Please suggest me.

Thursday, November 29, 2012 - 3:42:40 AM - sayeedpilot Back To Top (20621)

 

SELECT

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    , [sJSTP].[step_uid] AS [StepID]

    , [sJSTP].[step_id] AS [StepNo]

    , [sJSTP].[step_name] AS [StepName]

    , CASE [sJSTP].[subsystem]

        WHEN 'ActiveScripting' THEN 'ActiveX Script'

        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

        WHEN 'PowerShell' THEN 'PowerShell'

        WHEN 'Distribution' THEN 'Replication Distributor'

        WHEN 'Merge' THEN 'Replication Merge'

        WHEN 'QueueReader' THEN 'Replication Queue Reader'

        WHEN 'Snapshot' THEN 'Replication Snapshot'

        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

        ELSE sJSTP.subsystem

      END AS [StepType]

    , [sPROX].[name] AS [RunAs]

    , [sJSTP].[database_name] AS [Database]

    , [sJSTP].[command] AS [ExecutableCommand]

    , CASE [sJSTP].[on_success_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: ' 

                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) 

                    + ' ' 

                    + [sOSSTP].[step_name]

      END AS [OnSuccessAction]

    , [sJSTP].[retry_attempts] AS [RetryAttempts]

    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]

    , CASE [sJSTP].[on_fail_action]

        WHEN 1 THEN 'Quit the job reporting success'

        WHEN 2 THEN 'Quit the job reporting failure'

        WHEN 3 THEN 'Go to the next step'

        WHEN 4 THEN 'Go to Step: ' 

                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) 

                    + ' ' 

                    + [sOFSTP].[step_name]

      END AS [OnFailureAction]

FROM

    [msdb].[dbo].[sysjobsteps] AS [sJSTP]

    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]

        ON [sJSTP].[job_id] = [sJOB].[job_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]

        ON [sJSTP].[job_id] = [sOSSTP].[job_id]

        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]

        ON [sJSTP].[job_id] = [sOFSTP].[job_id]

        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]

        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

ORDER BY [JobName], [StepNo]

 

This one is not working on SQL 2000 box , getting the following output.

 

Msg 208, Level 16, State 1, Line 2

Invalid object name 'msdb.dbo.sysproxies'.

 

Suggestions please..!

Thursday, November 8, 2012 - 1:26:20 PM - Rudy Rodarte Back To Top (20261)

I had a case where several jobs had an eMail was hard coded in T-SQL. I was able to query the command text of every job in one pass. Thanks for this post!  

Friday, November 2, 2012 - 10:35:18 AM - Bitfarmer Back To Top (20193)

I'm trying to query the notification settings of a job.  Need an easy way to find jobs that are notifying a user that isn't here any more.

Thursday, October 18, 2012 - 3:28:54 AM - Filip Back To Top (19967)

Masterpiece!

Wednesday, July 25, 2012 - 12:16:32 AM - CCDiane Back To Top (18790)

I really appreciate it. Very useful!

Many thanks!

Thursday, July 19, 2012 - 6:07:34 PM - SSISNewbie Back To Top (18669)

Thanks, You saved my day!

 

Friday, July 13, 2012 - 4:22:35 PM - Luan Back To Top (18507)

Great Job!

Tuesday, July 10, 2012 - 12:37:35 AM - kala Back To Top (18410)

Hi sir,

 

I need querey for ssis Package execute information load it into Sql server table autometically like

ID,Ownername,start date,enddate,descrition etc

 

thank'u

Friday, June 22, 2012 - 6:13:04 AM - VJ Back To Top (18174)

 

1. I need to Query to fetch all the job ran and in progress between dates

2. I need a Query to fetch duration for a job between dates

 

 

Friday, June 22, 2012 - 6:10:53 AM - VJ Back To Top (18173)

Thanks for the query....

I have tested the query for jobs scheduled from midnigh 12 AM to and current time 6 AM....I expected that all the jobs passed and in progress till 6 AM would be displayed...but above query only gave result of all jobs scheduled at 00:00:00

 

 

Wednesday, June 13, 2012 - 10:00:54 AM - Ravi Back To Top (17969)

Thanks a lot :)

Monday, June 11, 2012 - 8:36:42 AM - Biswajeet Back To Top (17925)

 

Thanks sir for this Query.     

 

 

 

 

 

Wednesday, June 6, 2012 - 11:54:09 AM - Derik Hammer Back To Top (17816)

I would like to recommend that in the first script for "SQL Server Agent Job Setup and Configuration Information" that the database_principals be replaced with server_principals. This is because I ended up getting NULL values in the job owner field since the login that was the owner of the job wasn't a user in the databases that I selected. The server_principals replacement will prevent the NULL situation.

Tuesday, May 29, 2012 - 8:07:18 AM - Dattatrey Sindol (Datta) Back To Top (17700)

I guess the above query had some spacing issues. Please take the below query.

SELECT JobID, JobName, StepID, StepNo, StepName, LastRunStatus, [LastRunDuration (HH:MM:SS)], LastRunRetryAttempts, LastRunDateTime
FROM
(
 SELECT
  [sJOB].[job_id] AS [JobID]
  , [sJOB].[name] AS [JobName]
  , [sJSTP].[step_uid] AS [StepID]
  , [sJSTP].[step_id] AS [StepNo]
  , [sJSTP].[step_name] AS [StepName]
  , CASE [sJSTP].[last_run_outcome]
   WHEN 0 THEN 'Failed'
   WHEN 1 THEN 'Succeeded'
   WHEN 2 THEN 'Retry'
   WHEN 3 THEN 'Canceled'
   WHEN 5 THEN 'Unknown'
    END AS [LastRunStatus]
  , STUFF(
    STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)),  6)
     , 3, 0, ':')
    , 6, 0, ':')
    AS [LastRunDuration (HH:MM:SS)]
  , [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
  , CASE [sJSTP].[last_run_date]
   WHEN 0 THEN NULL
   ELSE
    CAST(
     CAST([sJSTP].[last_run_date] AS CHAR(8))
     + ' '
     + STUFF(
      STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),  6)
       , 3, 0, ':')
      , 6, 0, ':')
     AS DATETIME)
    END AS [LastRunDateTime]
 FROM
  [msdb].[dbo].[sysjobsteps] AS [sJSTP]
  INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
   ON [sJSTP].[job_id] = [sJOB].[job_id]
 WHERE [sJSTP].[subsystem] = 'SSIS'
) Tmp
WHERE [LastRunDateTime] BETWEEN '2012-05-01' AND '2012-05-28'
ORDER BY [JobName], [StepNo]

Tuesday, May 29, 2012 - 8:05:27 AM - Dattatrey Sindol (Datta) Back To Top (17699)

Hi There,

You can use the following query to get the execution history for SSIS Packages between two specific date/time values.

 

SELECT

JobID, JobName, StepID, StepNo, StepName, LastRunStatus, [LastRunDuration (HH:MM:SS)], LastRunRetryAttempts, LastRunDateTime

FROM

 

(

SELECT

[sJOB]

.[job_id] AS [JobID], [sJOB].[name] AS [JobName], [sJSTP].[step_uid] AS [StepID], [sJSTP].[step_id] AS [StepNo], [sJSTP].[step_name] AS [StepName],CASE [sJSTP].[last_run_outcome]WHEN 0 THEN'Failed'WHEN 1 THEN'Succeeded'WHEN 2 THEN'Retry'WHEN 3 THEN'Canceled'WHEN 5 THEN'Unknown'ENDAS [LastRunStatus],STUFF(STUFF(RIGHT('000000'+CAST([sJSTP].[last_run_duration] ASVARCHAR(6)), 6), 3, 0,':'), 6, 0,':')AS [LastRunDuration (HH:MM:SS)], [sJSTP].[last_run_retries] AS [LastRunRetryAttempts],CASE [sJSTP].[last_run_date]WHEN 0 THENNULLELSECAST(CAST([sJSTP].[last_run_date] ASCHAR(8))+' '+STUFF(STUFF(RIGHT('000000'+CAST([sJSTP].[last_run_time] ASVARCHAR(6)), 6), 3, 0,':'), 6, 0,':')ASDATETIME)ENDAS [LastRunDateTime]FROM

[msdb]

.[dbo].[sysjobsteps] AS [sJSTP]INNERJOIN [msdb].[dbo].[sysjobs] AS [sJOB]ON [sJSTP].[job_id] = [sJOB].[job_id]WHERE [sJSTP].[subsystem] ='SSIS'

)

Tmp

WHERE

[LastRunDateTime] BETWEEN'2012-05-01'AND

'2012-05-28'

ORDER

BY [JobName], [StepNo] 

However, as far as the schedule is concerned, it is at the Job Level and not at the step level. For schedule level information, you can use the query under the heading "SQL Server Agent Job Schedule Information".

Hope that helps.

 

Saturday, May 26, 2012 - 6:48:51 PM - India Back To Top (17670)

can you help me with query to list all the ssis packages executed between a time / stamp and also some more details like recurring every 5 mins / 1 hr etc.,

Monday, April 9, 2012 - 5:28:17 AM - Dattatrey Sindol Back To Top (16815)

Hi Balaji,

For your scenario, you can approach something like this.
Say your job is scheduled to run once every day (You can find out the schedule associated with a job using the above query marked "SQL Server Agent Job Schedule Information"). You can build one table (similar to a time dimension) with one record for each day, call it as tblDate. Now let us say you use the query marked "SQL Server Agent Job Execution Information" to get the execution details of the job, let's call this query output as tblJobExecutionDetails. Now do a join something like "tblDate AS D LEFT JOIN tblJobExecutionDetails AS JED ON D.Date = CAST(JED.LastRunDateTime AS DATE)". For which ever date, you get a NULL value from the tblJobExecutionDetails, it represents those skipped runs what you mentioned in your comment.

Hope that helps.

Best Regards,
Dattatrey Sindol (Datta)
http://dattatreysindol.com

Tuesday, April 3, 2012 - 11:59:20 PM - Balaji Back To Top (16763)

Can anyone please help me!!!

Now we monitoring the jobs by manually.Am trying to monitor it automatically.So i

built query to find status of all jobs.And my query finds well all the status of

jobs such as(completed,failed,cancelled,rerunned) but except skipped jobs.Yes my

query couldn't find the skipped jobs details from the history.

 

Skipped jobs means all jobs scheduled to execute in a particular time but

unfortunately some jobs may not getting executed at the scheduled time.So also I

need to retrieve these criteria records in my automation query.Because of this

issue my task was in pending status for the past 3 months.Please give ur

assisstance if any1 knows,


 

Tuesday, March 27, 2012 - 8:47:18 AM - Prasana Back To Top (16634)

Good one man :)

Saturday, January 28, 2012 - 5:53:26 AM - Dattatrey Sindol Back To Top (15817)

Thanks everyone for the feedback!

 

Best Regards,

Dattatrey Sindol (Datta)

Tuesday, January 3, 2012 - 3:27:31 PM - Willis Johnson Back To Top (15506)

Exceptionally helpful. Kudos!

Monday, January 2, 2012 - 11:09:45 AM - Stephane Thinel Back To Top (15489)

I will keep this for future references.  Thank you! 


Friday, December 9, 2011 - 11:57:43 AM - The Happy DBA Back To Top (15329)

Amazing information! Very useful, very complete! Thanks a lot!

Friday, December 9, 2011 - 8:44:27 AM - PJ Back To Top (15325)

Great set of scripts.














get free sql tips
agree to terms