November 25, 2015 at 2:27 pm
Hi,
I am working to set up a job to send notification about the long running jobs.
If I have a db refresh job, daily it completes in 2-3 hrs but today it is running more 3.5 hrs, I need to get alert.
In instance there are 4-5 jobs, I want to compare each job with the approximate complete duration and send alert if that job is running longer
November 25, 2015 at 2:57 pm
You could track the times in sys.jobactivity. You would need to create an average, or max, or whatever statistical calculation you want. Then you'd need to track the current execution in a job of some sort, calculate duration, and alert yourself with an email or a raised alert, if things were running slow.
not hard, but complex. You might also need to do this from a remote machine, as if you are having SQL Agent issues on an instance, this might not be reliable to check on job execution. Perhaps a Windows Task could work here on the local machine.
This is one of the things we provide in SQL Monitor from Redgate. We track the execution duration for jobs and alert you if the jobs are taking too long to run.
Disclosure: I work for Redgate Software.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 25, 2015 at 3:06 pm
If you have 10 records for that job so far, 7 times ran in 2.5 hrs one run 3 hrs, another run 5 hr another run 6 hr, I think max gives you 6hr. If you took average, I think it will add all run times /10.
But I am looking some thing like if it is running more than 2.5 hr i.e. which is the most of the run completed, the I need to get alert.
November 25, 2015 at 3:31 pm
You need to build two things.
1. How do I examine the data and determine if the current execution is long. Meaning, am I looking at the mode, the average, weighted average, something else. Comparing this to the current execution means looking in sys.jobactivity, calculating the current run time, and comparing it to your value.
2. You need a process to perform #1 and get executed. This could be a SQL Agent job that runs every 5 minutes, or a Windows scheduled task, or some other service.
There isn't a way to configure this in SQL Server. You need to buy or build software to do this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 26, 2015 at 8:43 am
This works for me...change @MaxMinutes as necessary and schedule to run as often as you set @MaxMinutes.
It will list anything long running, not just jobs!
set nocount on
go
declare @MaxMinutes int
set @MaxMinutes = 60
select 1
from sys.dm_exec_requests
where session_id in
(select distinct spid
from master..sysprocesses
where cmd not in ('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER','LOG WRITER','CHECKPOINT','CLEANUP','GHOST')
and status in ('runnable','running','suspended')
and spid <> @@SPID)
and isnull(DATEDIFF(mi, start_time , getdate()), 0) > @MaxMinutes
if @@rowcount > 0
begin
---- format results in an HTML table
declare @tableHTML NVARCHAR(MAX), @subject varchar(100)
set @subject = 'SQL requests running over an hour on ' + (select @@servername)
set @tableHTML =
N'<style>
table, tr, td {
font-family: arial;
font-size:10pt;
}
</style>'+
N'<font face="Arial" size="2">' +
N'<table border="1">' +
N'</font>' +
N'<tr>' +
N'<th>Command</th>' +
N'<th>Start Time</th>' +
N'<th>Runtime Minutes</th>' +
N'<th>Status</th>' +
N'<th>SPID</th>' +
N'<th>Blocking SPID</th>' +
N'<th>Login Name</th>' +
N'<th>Query - first 100 char</th>' +
N'<th>Wait Type</th>' +
N'<th>Wait Time Minutes</th>' +
N'</tr>' +
cast ((
select
'td/@align' = 'center',td = qs.command,'',
'td/@align' = 'center',td = convert(varchar(20), qs.start_time,120),'',
'td/@align' = 'center',td = datediff(n,qs.start_time,getdate()),'',
'td/@align' = 'center',td = qs.status,'',
'td/@align' = 'center',td = qs.session_id,'',
'td/@align' = 'center',td = qs.blocking_session_id,'',
'td/@align' = 'center',td = (select top 1 Loginame from master..sysprocesses where SPID = qs.session_id),'',
'td/@align' = 'center',td = left(st.text,200),'',
'td/@align' = 'center',td = qs.wait_type,'',
'td/@align' = 'center',td = (qs.wait_time/60000)
from sys.dm_exec_requests qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where qs.session_id in
(select distinct spid
from master..sysprocesses
where cmd not in ('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER','LOG WRITER','CHECKPOINT','CLEANUP','GHOST')
and status in ('runnable','running','suspended')
and spid <> @@SPID)
and isnull(DATEDIFF(mi, start_time , getdate()), 0) > @MaxMinutes
order by qs.start_time
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail Profile name',
@recipients = 'xxxxxxxxxxxx@xxxxxxxxxxx.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML'
end;
November 27, 2015 at 9:38 pm
try the following system tables which hold the various bits of information on the job
•msdb.dbo.SysJobs
•msdb.dbo.SysJobSteps
•msdb.dbo.SysJobSchedules
•msdb.dbo.SysJobServers
•msdb.dbo.SysJobHistory
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply