Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Job History - SQL Server

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[GetJobHistory]  
with execute as 'dbo' as
BEGIN
/*---------------------------------------------------------------------------------
Script to create the JOB table for this stored procedure:

CREATE TABLE [dbo].[JOB2] (
    [iJobId] [int] IDENTITY (1, 1) NOT NULL ,
    [sJobName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [iActive] [tinyint] NOT NULL,
    [sRecurUnit] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [iRecurNbr] [int] NULL,
    [iLastSched] [int] NULL,
    [dtmLastSched] [datetime] NULL 
    ) ON [PRIMARY]

---------------------------------------------------------------------------------*/
declare @dtmStartOfThisMonth datetime
declare @dtmStartOfLastMonth datetime

-- Inits --------------------------------------------------------------------------
set @dtmStartOfThisMonth = dateadd(d, 1 - day(getdate()), getdate())
set @dtmStartOfLastMonth = dateadd(m, -1, @dtmStartOfThisMonth)

-- Update [dtmLastSched] field on JOB records -------------------------------------
update job2
set dtmLastSched =
    (case sRecurUnit
    when 'D' then dateadd(d, -1, getdate())
    when 'M' then
        case 
            when day(getdate()) < iRecurNbr 
                then dateadd(d, iRecurNbr - 1, @dtmStartOfLastMonth)
            else dateadd(d, iRecurNbr - 1, @dtmStartOfThisMonth)
            end
        else null
        end
    )

-- Update [iLastSched] field on JOB records ----------------------------------------
update job2
set iLastSched = convert(int, convert(varchar(8), dtmLastSched, 112))

-- Compare JOB records to SYSJOBS and SYSJOBHISTORY records ------------------------
select
    job.sJobName,
    active = case iActive when 1 then 'yes' else 'no' end,
    status = case iActive when 1 
                then 
                    case when datediff(dd, job.dtmLastSched, getdate()) > 3 
                        then '(unknown)' 
                    when (datediff(dd, job.dtmLastSched, getdate()) > 1 
                        and job.sRecurUnit='M') then '(unknown)'
                    else  '*** NOT RUN ***' 
                    end 
                else 'not run' end,
    enabled = case  
        when enabled = 1 then 'yes' 
        when iActive = 1 then '*** NO ***' 
        else 'no' 
        end,
    run_date = 0,
    run_time = 0,
    message = '',
    step_id = 0

from 
    job2 job
    inner join msdb.dbo.sysjobs j
        on j.name = job.sJobName
where
    j.job_id not in (
        select job_id 
        from msdb.dbo.sysjobhistory 
        where run_date >= job.iLastSched
        )

union select 
    job.sJobName, 
    active = case iActive when 1 then 'yes' else 'no' end,
    status = CASE RUN_STATUS WHEN 0 THEN '*** ERROR ***' ELSE '(ok)' end,
    enabled = case  
        when enabled = 1 then 'yes' 
        when iActive = 1 then '*** NO ***' 
        else 'no' end,
    run_date, 
    run_time, 
    message,
    step_id

from 
    job2 job
    inner join msdb.dbo.sysjobs j
        on j.name = job.sJobName
    inner join msdb.dbo.sysjobhistory h
        on j.job_id = h.job_id
where 1=1
    and job.iActive = 1
    and run_date >= job.iLastSched
order by 
    run_date,
    job.sJobName,
    run_time, 
    step_id

end