PLEASE UPLOAD THE LATEST SCRIPT FROM <<HERE>>

 

Description

The Script "sp_GetJobDetails" has 3 result set which can be used to get job details and its history analysis result. Procedure gets Job_Id as parameter. However, the job_id is an optional parameter, we will be able to pass NULL to get complete job details.

 Hopefully, the script will be useful for the community as I have seen lots of Job related questions in  SQL Server forums. Here, I tried to accomodate few of system functions to get some basic info to avoid  lengthy code.

1. Basic Job details

          This section will produce the basic details of the job as follows:

                     1. Job ID                   - Id of the Job

                     2. Job Name              - Name of the Job

                     3. Enabled                 - Which will show us the job is active/enabled

                     4. Job Owner              - Owner of the Job

                     5. LastRunDateTime    - shows the last execution date and time

                     6. LastRunStatus        - shows the status of the last execution status

                     7. LastRunDuration     - shows the Duration of the last execution. This has been provided in a

                                                                    more friendly way of represnting in Time format(HH:MM:SS)

                     8. NextRunDateTime   - shows the next execution date and time

                     9. LastInvokedBy        - shows the User/Schedule ID by which the job has been invoked

                     10. CancelledBy          - shows the User by which the job has been invoked

                     11. Message               - Output message of the Job

 

2. Job History Analysis

          This section will fetch a history analysis result, how the job was performing for a period of time,

                                                and produce the pattern of MIN/MAX/AVG values for the Sampling as follows:

                    1. Job ID                      - Id of the Job

                    2. Job Name                 - Name of the Job

                    3. Avg Duration             - Average value of past executions for the sampling

                    4. Max Duration            - Maximum value of past executions for the sampling

                    5. Min Duration             - Minimum value of past executions for the sampling

                    6. Sampling                 - Sampling is the number of samples upon the calculation has been carried out.

 

3. Job Step details

         This section will produce the step wise details of the job as follows:

                    1. Parent Job Name       - Name of the Job.

                    2. StepID                      - Step id of the job

                    3. step_name                - Name of the Step

                    4. subsystem                - Name of the subsystem used by SQL Server Agent to execute the job step

                    5. Rundatetime             - Execution Date time for the step

                    6. Last_RunDuration       - shows the Duration of the last execution. This has been provided in a                                                                                                    more friendly way of represnting in Time format(HH:MM:SS)

 

Please download the script and have your suggestions.