May 11, 2015 at 1:23 pm
Hey all, Haven't posted here in a while!
I was hoping i could get some help on how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail report when the run duration exceeds 30 minutes.
The agent job in question kicks off our ETL, and it runs every hour. It's happened before where a job running elsewhere(Not best practice, but we run reports from our datawarehouse) creates a deadlock on 1 of the tables being updated in the ETL, but i only get notified when i get calls from end users saying their reports aren't returning results.
This would also be my first time setting up alerts on a SQL server so i'm not sure which method to use. I was thinking of creating a new job that query either the sysjobhistory or sysjobactivty table in msdb, but i would need it to refer to the run duration value of the job as it's still running( I think ink i can assume it's not updating that value every second...)
Any help is appreciated,
Thank you!
May 13, 2015 at 9:44 am
We use the following in a job which runs every hour. There may and probably will be better ways of doing it but it works for us.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @currently_running_jobs TABLE
(
job_id UNIQUEIDENTIFIER NOT NULL ,
last_run_date INT NOT NULL ,
last_run_time INT NOT NULL ,
next_run_date INT NOT NULL ,
next_run_time INT NOT NULL ,
next_run_schedule_id INT NOT NULL ,
requested_to_run INT NOT NULL ,
request_source INT NOT NULL ,
request_source_id SYSNAME NULL ,
running INT NOT NULL ,
current_step INT NOT NULL ,
current_retry_attempt INT NOT NULL ,
job_state INT NOT NULL
)
DECLARE @LongJobs TABLE
(
JobName NVARCHAR(255) ,
MostRecentStartTime DATETIME ,
ElapsedSeconds INT ,
CurrentStep NVARCHAR(255)
)
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, '';
WITH CurrentRunningJobs
AS ( SELECT crj.job_id ,
MAX(start_execution_date) AS MostRecentStartTime ,
DATEDIFF(ss, MAX(start_execution_date), GETDATE()) AS ElapsedSeconds ,
sjs.step_name
FROM @currently_running_jobs crj
INNER JOIN msdb..sysjobactivity sa ON crj.job_id = sa.job_id
INNER JOIN msdb..sysjobsteps sjs ON crj.job_id = sjs.job_id
AND crj.current_step = sjs.step_id
WHERE running = 1
GROUP BY crj.job_id ,
sjs.step_name
)
INSERT INTO @LongJobs
( JobName ,
MostRecentStartTime ,
ElapsedSeconds ,
CurrentStep
)
SELECT sj.name ,
crj.MostRecentStartTime ,
crj.ElapsedSeconds ,
crj.step_name
FROM CurrentRunningJobs crj
INNER JOIN msdb..sysjobs sj ON crj.job_id = sj.job_id
WHERE ElapsedSeconds > 3600
DECLARE @HasLongRunning INT = ( SELECT COUNT(*)
FROM @LongJobs
)
DECLARE @EmailText NVARCHAR(MAX)
SET @EmailText = '<h3>The following have been identified as long running</h3>
'
DECLARE @JobName NVARCHAR(255)
DECLARE @StepName NVARCHAR(255)
DECLARE @Duration NVARCHAR(100)
IF @HasLongRunning > 0
BEGIN
WHILE ( SELECT COUNT(*)
FROM @LongJobs
) > 0
BEGIN
SELECT TOP 1
@JobName = JobName ,
@StepName = ISNULL(CurrentStep, 'Not Avail') ,
@Duration = CAST(ElapsedSeconds / 60 AS NVARCHAR(100))
FROM @LongJobs
ORDER BY JobName
SET @EmailText = @EmailText
+ '<font face = "Courier New"><h5>Job Name..........: '
+ @JobName + '
' + 'Current Step......: ' + @StepName
+ '
Current Duration..: ' + @Duration
+ ' (minutes)
'
+ '-----------------------------------------------------------------</h5></font>
'
DELETE FROM @LongJobs
WHERE JobName = @JobName
END
DECLARE @subject NVARCHAR(MAX) = 'DBA ALERT - ' + @@SERVERNAME
+ ' - Long Running Job(s) Identified'
EXEC msdb.dbo.sp_send_dbmail @profile_name = NULL,
@recipients = 'someone@me.com', @copy_recipients = '',
@blind_copy_recipients = '',
@subject = @subject ,
@body = @EmailText, @body_format = 'HTML', @importance = 'High'
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply