Copy the below code and schedule the job on the server :-
DECLARE @INT INT
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(23),ja.start_execution_date,121) AS [Start_execution_date]
, ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running') AS [Stop_execution_date]
,DATEDIFF(SECOND,CONVERT(VARCHAR(23),ja.start_execution_date,121),ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121),GETDATE())) as Duration
into #jobhist
FROM msdb.dbo.sysjobs jobs
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL
--where jobs.name not like 'repli%' and name not like '%mirr%' and name not like '%distribu%'and name not like '%subscript%' and name not like '%sys%' and name not like '%pub%'
select *
, convert(varchar(10), (Duration/86400)) + ':' +
convert(varchar(10), ((Duration%86400)/3600)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)/60)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)%60)) as 'DD:HH:MM:SS'
into #JH
from #jobhist where stop_execution_date='Is Running' and start_execution_date is not null
DECLARE @tableHTML NVARCHAR(MAX)
declare @NumStDevs int = 2
SET @tableHTML =
N'
N'' +
' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBPROFILENAME',
@recipients='EMAILS',
@subject = 'LongRunning Jobs',
@body = @tableHTML,
@body_format = 'HTML' ;
drop table #jobhist
drop table #jh
DECLARE @INT INT
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(23),ja.start_execution_date,121) AS [Start_execution_date]
, ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running') AS [Stop_execution_date]
,DATEDIFF(SECOND,CONVERT(VARCHAR(23),ja.start_execution_date,121),ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121),GETDATE())) as Duration
into #jobhist
FROM msdb.dbo.sysjobs jobs
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL
--where jobs.name not like 'repli%' and name not like '%mirr%' and name not like '%distribu%'and name not like '%subscript%' and name not like '%sys%' and name not like '%pub%'
select *
, convert(varchar(10), (Duration/86400)) + ':' +
convert(varchar(10), ((Duration%86400)/3600)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)/60)) + ':'+
convert(varchar(10), (((Duration%86400)%3600)%60)) as 'DD:HH:MM:SS'
into #JH
from #jobhist where stop_execution_date='Is Running' and start_execution_date is not null
DECLARE @tableHTML NVARCHAR(MAX)
declare @NumStDevs int = 2
SET @tableHTML =
N'
' + @@SERVERNAME + ': Long Running Jobs Alert :
' +N'
Job Name | start_execution_date | Current_Status | ' +Duration[DD:HH:MM:SS] | ' +
---|
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBPROFILENAME',
@recipients='EMAILS',
@subject = 'LongRunning Jobs',
@body = @tableHTML,
@body_format = 'HTML' ;
drop table #jobhist
drop table #jh
No comments:
Post a Comment