--TABLE
USE [DBACollection]
GO
/****** Object: Table [dbo].[JobHistoryCollection] Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JobHistoryCollection](
[ServerName] [nvarchar](128) NULL,
[JobName] [sysname] NULL,
[Enabled] [tinyint] NULL,
[CurrentStatus] [varchar](9) NULL,
[CurrentStepNbr] [int] NULL,
[LastRunTime] [datetime] NULL,
[LastRunOutcome] [varchar](11) NULL,
[LastRunDuration] [int] NULL,
[CollectionTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[JobHistoryCollection] ADD CONSTRAINT [DF_JobHistoryCollection_CollectionTime] DEFAULT (getdate()) FOR [CollectionTime]
GO
--PROC
USE [DBACollection]
GO
/*
exec pr_JobHistoryCollection
select * from JobHistoryCollection order by collectiontime desc
select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc
*/
alter proc pr_JobHistoryCollection
as
begin
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
if(object_id('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * from #tmp
insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection
end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec sp_JobHistoryCollection',
@database_name=N'DBACollection',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20131126,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
