Share via

SQL Server: Job Activity Monitoring by SQL Script

  • Article

Issue:
Admin needs to send jobs status report to managers by email after every specific interval of time same as SQL Server builtin job activity console.
**
Solution:**
Below is the store procedure that gets status of all jobs if Job_Id is null or get specific if sends Job_Id.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_GetJobDetails] (@job_id uniqueidentifier = NULL)
As  
Begin  

--Varibale Declarations
declare @num_days int
declare @first_day datetime, @last_day datetime
declare @first_num int

if @num_days is null
set @num_days=30

set @last_day = getdate()
set @first_day = dateadd(dd, -@num_days, @last_day)

select @first_num= cast(year(@first_day) as char(4))
+replicate('0',2-len(month(@first_day)))+ cast(month(@first_day) as varchar(2))
+replicate('0',2-len(day(@first_day)))+ cast(day(@first_day) as varchar(2))

--Basic Job Information  
;With bjinfo as
(
Select 
A.job_id, B.name, Case B.enabled
When 1 Then 'Yes'
When 0 Then 'No' Else 'Unknown' End As 'Enabled',
/* -- Only for later version of SQL Server 2005 */
msdb.dbo.SQLAGENT_SUSER_SNAME(b.owner_sid) 'Job Owner',  
(Select Top 1 next_scheduled_run_date From msdb.dbo.sysjobactivity Where job_id = A.job_id Order by session_id desc) as 'NextRunDateTime',
msdb.dbo.agent_datetime(last_run_date, last_run_time) as 'LastRunDateTime',  
Case last_run_outcome
When 0 Then 'Failed'  
When 1 Then 'Succeeded'
When 2 Then 'Retry'
When 3 Then 'Cancelled'
Else 'NA' 
End Last_Run_Status, 
last_run_duration,
case
when (len(cast(last_run_duration as varchar(20))) < 3)  
then cast(last_run_duration as varchar(6))
WHEN (len(cast(last_run_duration as varchar(20))) = 3)  
then LEFT(cast(last_run_duration as varchar(6)),1) * 60  

  • RIGHT(cast(last_run_duration as varchar(6)),2)  
    WHEN (len(cast(last_run_duration as varchar(20))) = 4)  
    then LEFT(cast(last_run_duration as varchar(6)),2) * 60   
  • RIGHT(cast(last_run_duration as varchar(6)),2)  
    WHEN (len(cast(last_run_duration as varchar(20))) >= 5)  
    then (Left(cast(last_run_duration as varchar(20)),len(last_run_duration)-4)) * 3600   
    +(substring(cast(last_run_duration as varchar(20)) , len(last_run_duration)-3, 2)) * 60
  • Right(cast(last_run_duration as varchar(20)) , 2)
    End As 'Last_RunDuration',
    CONVERT(DATETIME, RTRIM(last_run_date)) + ((last_run_time + last_run_duration) * 9 + (last_run_time + last_run_duration) % 10000 * 6 
  • (last_run_time + last_run_duration) % 100 * 10) / 216e4 AS Last_RunFinishDateTime,
    Case last_run_outcome
    When 1 Then  
    Left(Replace(last_outcome_message,'The job succeeded.  The Job was invoked by',''),  
    Charindex('.',Replace(last_outcome_message,'The job succeeded.  The Job was invoked by','')))   
    When 0 Then  
    Left(Replace(last_outcome_message,'The job failed.  The Job was invoked by',''),  
    Charindex('.',Replace(last_outcome_message,'The job failed.  The Job was invoked by','')))   
    When 3 Then  
    Left(Replace(last_outcome_message,'The job was stopped prior to completion by ',''),  
    Charindex('.',Replace(last_outcome_message,'The job was stopped prior to completion by ','')))   
    End 'LastInvokedBy',  
    Case last_run_outcome
    When 3 Then  
    Left(Replace(last_outcome_message,'The job failed.  The Job was invoked by',''),  
    Charindex('.',Replace(last_outcome_message,'The job failed.  The Job was invoked by','')))   
    Else ''
    End 'Cancelled/Stopped By', 
    last_outcome_message 'Message'  
    From msdb.dbo.sysjobs B   
    Left Join (select job_id,last_run_outcome,last_outcome_message,case when last_run_date = 0
                                                          then 19900101
      else
      last_run_date end last_run_date,last_run_time,last_run_duration from msdb.dbo.SysJobServers) A on A.job_id = B.job_id
    Where ((A.job_id = @job_id  and @Job_id is not null) OR (1=1 and @Job_id is null)) 
    ),
    bjhistory as
    ( select
    jobhist.job_id,
    jobs.name,
    jobhist.step_id,
    run_dur_Casted = case 
    when (len(cast(jobhist.run_duration as varchar(20))) < 3)  
    then cast(jobhist.run_duration as varchar(6))
    WHEN (len(cast(jobhist.run_duration as varchar(20))) = 3)  
    then LEFT(cast(jobhist.run_duration as varchar(6)),1) * 60   
  • RIGHT(cast(jobhist.run_duration as varchar(6)),2)  
    WHEN (len(cast(jobhist.run_duration as varchar(20))) = 4)  
    then LEFT(cast(jobhist.run_duration as varchar(6)),2) * 60   
  • RIGHT(cast(jobhist.run_duration as varchar(6)),2) 
    WHEN (len(cast(jobhist.run_duration as varchar(20))) >= 5)  
    then (Left(cast(jobhist.run_duration as varchar(20)),len(jobhist.run_duration)-4)) * 3600   
    +(substring(cast(jobhist.run_duration as varchar(20)) , len(jobhist.run_duration)-3, 2)) * 60
  • Right(cast(jobhist.run_duration as varchar(20)) , 2)
    end
    from msdb.dbo.sysjobhistory jobhist
    Inner Join msdb.dbo.sysjobs jobs On jobhist.job_id = jobs.job_id
    where jobhist.job_id=jobs.job_id
    and jobhist.run_date>= @first_num
    and jobhist.step_id=0
    and ((jobs.job_id = @job_id  and @Job_id is not null) OR (1=1 and @Job_id is null)) 
    )
    ,bjstats
    As
    (
    Select jobs.job_id
    ,jobs.name
    ,'Sampling'=(select count(*) from bjhistory jobhist where jobhist.job_id=jobs.job_id)
    ,'run_dur_max'=(select max(run_dur_Casted) from bjhistory jobhist where jobhist.job_id=jobs.job_id)
    ,'run_dur_min'=(select min(run_dur_Casted) from bjhistory jobhist where jobhist.job_id=jobs.job_id)
    ,'run_dur_avg'=(select avg(run_dur_Casted) from bjhistory jobhist where jobhist.job_id=jobs.job_id)
    from msdb..sysjobs jobs
    Where ((jobs.job_id = @job_id  and @Job_id is not null) OR (1=1 and @Job_id is null))
    )
       select bjinfo.Name as 'Job_Name', bjinfo.Enabled,bjinfo.Last_Run_Status [Current Run Status], 
    bjinfo.LastRunDateTime [Current Run StartTime],bjinfo.Last_RunFinishDateTime [Current Run EndTime],
    Right('00'+cast(bjinfo.Last_RunDuration/3600 as varchar(10)),2)
    +':'+replicate('0',2-len((bjinfo.Last_RunDuration % 3600)/60))+cast((bjinfo.Last_RunDuration % 3600)/60 as varchar(2))
    +':'+replicate('0',2-len((bjinfo.Last_RunDuration % 3600) %60))+cast((bjinfo.Last_RunDuration % 3600)%60 as varchar(2)) 'Currnet Run Duration',
    'Avg. Duration' = cast(run_dur_avg/3600 as varchar(10))
    +':'+replicate('0',2-len((run_dur_avg % 3600)/60))+cast((run_dur_avg % 3600)/60 as varchar(2))
    +':'+replicate('0',2-len((run_dur_avg % 3600) %60))+cast((run_dur_avg % 3600)%60 as varchar(2)),
    'Max. Duration' = cast(run_dur_max/3600 as varchar(10))
    +':'+replicate('0',2-len((run_dur_max % 3600)/60))+cast((run_dur_max % 3600)/60 as varchar(2))
    +':'+replicate('0',2-len((run_dur_max % 3600) %60))+cast((run_dur_max % 3600)%60 as varchar(2)),
    'Min. Duration' = cast(run_dur_min/3600 as varchar(10))
    +':'+replicate('0',2-len((run_dur_min % 3600)/60))+cast((run_dur_min % 3600)/60 as varchar(2))
    +':'+replicate('0',2-len((run_dur_min % 3600) %60))+cast((run_dur_min % 3600)%60 as varchar(2)),
            bjstats.Sampling,bjinfo.NextRunDateTime
    From bjinfo
    left outer join bjstats on bjinfo.job_id = bjstats.job_id
    where bjinfo.name like 'DAX%' OR bjinfo.name like 'SNAPSHOT%'
    Order by bjinfo.name 
    If (@job_id is not null)
    Begin
    ;With CteJobHistory
    As
    (
    Select jobs.job_id,name,
    Case when run_status = 0 Then
    (Select Top 1 message From msdb.dbo.sysjobhistory A
    Where A.job_id = jobs.job_id and A.run_date = jobhist.run_date and A.run_time = jobhist.run_time
    and step_id = 1
    )
    Else jobhist.Message
    End Message
    ,msdb.dbo.agent_datetime(run_date,run_time) run_datetime,
    case
    when (len(cast(run_duration as varchar(20))) < 3)  
    then cast(run_duration as varchar(6))
    WHEN (len(cast(run_duration as varchar(20))) = 3)  
    then LEFT(cast(run_duration as varchar(6)),1) * 60  
  • RIGHT(cast(run_duration as varchar(6)),2)  
    WHEN (len(cast(run_duration as varchar(20))) = 4)  
    then LEFT(cast(run_duration as varchar(6)),2) * 60   
  • RIGHT(cast(run_duration as varchar(6)),2)  
    WHEN (len(cast(run_duration as varchar(20))) >= 5)  
    then (Left(cast(run_duration as varchar(20)),len(run_duration)-4)) * 3600   
    +(substring(cast(run_duration as varchar(20)) , len(run_duration)-3, 2)) * 60
  • Right(cast(run_duration as varchar(20)) , 2)
    End As 'RunDuration',
    CONVERT(DATETIME, RTRIM(run_date)) + ((run_time + run_duration) * 9 + (run_time + run_duration) % 10000 * 6 
  • (run_time + run_duration) % 100 * 10) / 216e4 AS RunFinishDateTime,
    Case run_status
    When 0 Then 'Failed'  
    When 1 Then 'Succeeded'
    When 2 Then 'Retry'
    When 3 Then 'Cancelled'
    Else 'NA' 
    End Last_Run_Status
    from msdb.dbo.sysjobhistory jobhist
    Inner Join msdb.dbo.sysjobs jobs On jobhist.job_id = jobs.job_id
    Where jobs.job_id = @job_id and step_id =0
    )Select job_id,name,message,run_datetime,
    Right('00'+cast(RunDuration/3600 as varchar(10)),2)
    +':'+replicate('0',2-len((RunDuration % 3600)/60))+cast((RunDuration % 3600)/60 as varchar(2))
    +':'+replicate('0',2-len((RunDuration % 3600) %60))+cast((RunDuration % 3600)%60 as varchar(2)) 'RunDuration', RunFinishDateTime
    Last_Run_Status From CteJobHistory
    Order by run_datetime desc
    End
    End