Learn more about SQL Server tools

   
   








Learn more about SQL Server tools








Learn more about SQL Server tools


Querying SQL Server Agent Job Information

MSSQLTips author Dattatrey Sindol By:   |   Read Comments (54)   |   Related Tips: More > 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:


Last Update: 12/9/2011


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, November 20, 2014 - 5:53:55 AM - Jase Read The Tip

Fantastic post Dattarey, thank you.


Tuesday, October 07, 2014 - 4:38:31 AM - Bikram Pattanayak Read The Tip

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 Read The Tip

Excellent Post!!! Thanks 


Monday, April 28, 2014 - 3:57:00 PM - KillerDBA Read The Tip

 

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 06, 2014 - 3:15:34 PM - Dattatrey Sindol (Datta) Read The Tip

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 02, 2014 - 12:11:31 AM - MSSQL DBA Read The Tip

hi,

I want deleted job log of sql agent ??

 

Regards,

DBA


Friday, March 14, 2014 - 2:28:54 AM - RehanPattni Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

Great scripts, really helped me out. Thanks!


Tuesday, November 12, 2013 - 8:58:36 PM - Dattatrey Sindol Read The Tip

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 Read The Tip

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 05, 2013 - 3:05:09 PM - Patricia Beyer Read The Tip

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 Read The Tip

Great scripts. THANK YOU VERY MUCH!


Friday, June 21, 2013 - 1:00:49 PM - Joey Read The Tip

Datta,

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


Wednesday, June 05, 2013 - 11:56:02 AM - RyanWooster Read The Tip

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 Read The Tip

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 Read The Tip

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 05, 2013 - 3:08:06 PM - Ritesh Sheth Read The Tip

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 01, 2013 - 12:05:42 PM - Jacque Read The Tip

Dattatrey - you are a life saver.

Thanks! 


Thursday, January 31, 2013 - 12:15:35 PM - fairy Read The Tip

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 Read The Tip

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 Read The Tip

 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 Read The Tip

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 Read The Tip

 

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 Read The Tip

Hi Dattatrey Sindol,

Thankkyou very much. 

Its helped me alot.


Thanks



Monday, January 14, 2013 - 7:59:52 PM - chaithanya Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2013 - 2:07:08 PM - chaithanya Read The Tip

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 Read The Tip

 

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 08, 2012 - 1:26:20 PM - Rudy Rodarte Read The Tip

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 02, 2012 - 10:35:18 AM - Bitfarmer Read The Tip

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 Read The Tip

Masterpiece!


Wednesday, July 25, 2012 - 12:16:32 AM - CCDiane Read The Tip

I really appreciate it. Very useful!

Many thanks!


Thursday, July 19, 2012 - 6:07:34 PM - SSISNewbie Read The Tip

Thanks, You saved my day!

 


Friday, July 13, 2012 - 4:22:35 PM - Luan Read The Tip

Great Job!


Tuesday, July 10, 2012 - 12:37:35 AM - kala Read The Tip

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 Read The Tip

 

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 Read The Tip

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 Read The Tip

Thanks a lot :)


Monday, June 11, 2012 - 8:36:42 AM - Biswajeet Read The Tip

 

Thanks sir for this Query.     

 

 

 

 

 


Wednesday, June 06, 2012 - 11:54:09 AM - Derik Hammer Read The Tip

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) Read The Tip

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) Read The Tip

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 Read The Tip

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 09, 2012 - 5:28:17 AM - Dattatrey Sindol Read The Tip

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 03, 2012 - 11:59:20 PM - Balaji Read The Tip

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 Read The Tip

Good one man :)


Saturday, January 28, 2012 - 5:53:26 AM - Dattatrey Sindol Read The Tip

Thanks everyone for the feedback!

 

Best Regards,

Dattatrey Sindol (Datta)


Tuesday, January 03, 2012 - 3:27:31 PM - Willis Johnson Read The Tip

Exceptionally helpful. Kudos!


Monday, January 02, 2012 - 11:09:45 AM - Stephane Thinel Read The Tip

I will keep this for future references.  Thank you! 


Friday, December 09, 2011 - 11:57:43 AM - The Happy DBA Read The Tip

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


Friday, December 09, 2011 - 8:44:27 AM - PJ Read The Tip

Great set of scripts.




 

Sponsor Information