Monitoring job history using Reporting Services
Easily monitoring your SQL Agent job history using SSRS for concurrency, job lenght and job status.
2013-09-05
10,455 reads
/* This script adds empty first step for every job for easier job history viewing. By default, you can see information about job launch only after completion of first step. */ USE msdb; SET NOCOUNT ON; DECLARE @tmp_sp_help_jobstep TABLE ( step_id INT NULL , step_name NVARCHAR(128) NULL , subsystem NVARCHAR(128) COLLATE Latin1_General_CI_AS NULL , command NVARCHAR(MAX) NULL , flags INT NULL , cmdexec_success_code INT NULL , on_success_action TINYINT NULL , on_success_step_id INT NULL , on_fail_action TINYINT NULL , on_fail_step_id INT NULL , server NVARCHAR(128) NULL , database_name SYSNAME NULL , database_user_name SYSNAME NULL , retry_attempts INT NULL , retry_interval INT NULL , os_run_priority INT NULL , output_file_name NVARCHAR(300) NULL , last_run_outcome INT NULL , last_run_duration INT NULL , last_run_retries INT NULL , last_run_date INT NULL , last_run_time INT NULL , proxy_id INT NULL , job_id UNIQUEIDENTIFIER NULL ) DECLARE @cur_job_name VARCHAR(1000) DECLARE job_cursor CURSOR FOR SELECT [name] FROM msdb..sysjobs; OPEN job_cursor; FETCH NEXT FROM job_cursor INTO @cur_job_name; WHILE (@@FETCH_STATUS <> -1) BEGIN print @cur_job_name; delete from @tmp_sp_help_jobstep; BEGIN TRY INSERT INTO @tmp_sp_help_jobstep ( step_id , step_name , subsystem , command , flags , cmdexec_success_code , on_success_action , on_success_step_id , on_fail_action , on_fail_step_id , server , database_name , database_user_name , retry_attempts , retry_interval , os_run_priority , output_file_name , last_run_outcome , last_run_duration , last_run_retries , last_run_date , last_run_time , proxy_id ) EXEC dbo.sp_help_jobstep @job_name = @cur_job_name, @step_id = 1 ; END TRY BEGIN CATCH END CATCH; IF NOT EXISTS(SELECT * from @tmp_sp_help_jobstep WHERE step_name = N'start step') BEGIN EXEC sp_add_jobstep @job_name = @cur_job_name, @step_name = N'start step', @subsystem = N'TSQL', @command = N'-- start step', @step_id = 1, @on_success_action = 3, -- Go to next step @on_fail_action = 3; -- Go to next step PRINT ' start step added' END FETCH NEXT FROM job_cursor INTO @cur_job_name; END CLOSE job_cursor; DEALLOCATE job_cursor;