Microsoft SSIS

CAPTURE SQL AGENT JOB DOWNTIME STATISTICS

Purpose: Collecting Daily JOB Statistics to collect SUCCESS and FAILURE for a period of time…..

Solution: Create the following Store procedure in MASTER or MSDB. Replace the “PROD –
DAILY JOB” with JOB-Name specific to your work. Execute the JOB.

The following example is specifically created for the purpose of capturing the daily JOB status into a Table and produce reports and Dashboards with DOWN TIME measures….

USE
[msdb]

GO

 

/****** Object: StoredProcedure [dbo].[JobExecStatus] Script Date: 5/13/2014 3:53:17 PM ******/

SET
ANSI_NULLS
ON

GO

 

SET
QUOTED_IDENTIFIER
ON

GO

 

–Use MSDB

–go

 

CREATE
PROCEDURE
[dbo].[JobExecStatus]

AS

 

select
j.Name, Step_name, Run_status,
Cast(str([run_date])
as
datetime) as Run_date, run_duration,
Server,

case
run_status

    when
‘0’
then
‘Failed’

    when
‘1’
then
‘Succeeded’

    when
‘2’
then
‘Failed’

    when
‘3’
then
‘canceled’

End

Status


from
dbo.sysjobhistory
b


join

(

select
Job_id, name
from
dbo.sysjobs

 

where
name
=
‘PROD – DAILY JOB’)
J

 

on
J.job_id = b.job_id

 

where
j.name =
‘PROD – DAILY JOB’
and step_name

=
‘(Job outcome)’

and
Cast(str([run_date])
as
date)
=
Cast(GetDate()
as
Date)

 

GO


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s