Getting Information About the Installation and Running Information of the Job in SQL Server

Hello everyone,

In this article, I will give you information about how to setup and run job in SQL Server.

You can use the query below to learn about the installation and running of the jobs you have created in SQL Server.

Our query will bring us many information such as job name, command run by job for all jobs.

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];

When you run the query, if you have created a job before, you will see a result like the one below or you will see more information about more jobs.

As you can see, our query brought us various information about the installation and running of job. You can use it according to your need.

Good luck to everyone in business and life.