IF EXISTS (
SELECT
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
)
BEGIN
DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
--, @Now DATETIME
SET @DocumentHead = N'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'Currently Running Jobs on '+' '+ @@SERVERNAME + ' .'+ 'Please check any long running jobs and Also we should NOT have REBUILD INDEXES,REBUILD STATS, OR FULL BACKUPS running at this moment.' + ' '+ Convert(varchar(19),getdate())
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Server Name</th>'
+ N' <th>Job Name</th>'
+ N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],
@@SERVERNAME AS TD,
j.name AS job_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
FOR XML RAW('tr'), ELEMENTS);
IF @TableBody IS NULL
BEGIN
PRINT 'No Jobs running right now ';
RETURN;
END
ELSE
BEGIN
--DECLARE @AlertMessage VARCHAR(4000);
--DECLARE @RecipientsList VARCHAR(4000);
DECLARE @subject VARCHAR(4000);
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default'
,@recipients= 'abc@xyz.com'
,@body = @Document--@alertmessgae
,@body_format = 'HTML'
,@importance = 'Low'
,@subject = @subject
--SET @AlertMessage = 'Currently Running Jobs on '+' '+ @@SERVERNAME + ' '+ 'please check any long running jobs and also we should not have Rebuild Indexes,Rebuild Stats,or Full Backups running at this moment'
--SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME
END
END
No comments:
Post a Comment