November 3, 2015 at 2:38 pm
Hi All,
Need some tsql coding help on below ask.
How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.
For example) :
Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.
In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...
Looking if someone who can help in the tsql code part for the above ask.
Appreciate if someone can help me if they have already dealt with such scenarios.
Thanks in advance.
November 6, 2015 at 11:03 am
I would start by adding some code to log the start time and end time of your jobs in a table somewhere. Then you can query that for "normal"/average time and compare to that.
November 6, 2015 at 5:30 pm
pietlinden (11/6/2015)
I would start by adding some code to log the start time and end time of your jobs in a table somewhere. Then you can query that for "normal"/average time and compare to that.
Isn't that what the dbo.jobhistory table does auto-magically for us?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2015 at 5:44 pm
Yup... Looks like Gail's comment to the effect of "It takes a LONG time to learn the ins and outs of SQL Server" was right on target... <g>
November 8, 2015 at 11:24 am
I have been using this which I found a few years ago, and modified a bit.
/****** Object: StoredProcedure [dbo].[usp_LongRunningJobs_XML] Script Date: 11/08/2015 13:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: Devin Knight and Jorge Segarra
-- Create date: 7/6/2012
-- Description: Monitors currently running SQL Agent jobs and
-- alerts admins if runtime passes set threshold
-- Updates: 7/11/2012 Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''
--
Me 6-28-2013 Remove unneeded data in email. was 'SELECT RJ.*'
and format as XML
-- =============================================*/
CREATE PROCEDURE [dbo].[usp_LongRunningJobs_XML]
AS
--Set Mail Profile
DECLARE @MailProfile VARCHAR(50)
DECLARE @tableHTML NVARCHAR(MAX)
declare @BodyText varchar(150)
SET @MailProfile = (
SELECT @@SERVERNAME
) --Replace with your mail profile name
SET @MailProfile = 'DBA_Alerts'
set @BodyText = ' ' -- Should not be NULL
--Set Email Recipients
DECLARE @MailRecipients VARCHAR(50)
SET @MailRecipients = 'MyEmail@Email.com'
--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
DECLARE @JobLimitPercentage FLOAT
SET @JobLimitPercentage = 100 --Use whole percentages greater than 100
-- Create intermediate work tables for currently running jobs
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
,-- BOOL
request_source INT NOT NULL
,request_source_id SYSNAME COLLATE database_default NULL
,running INT NOT NULL
,-- BOOL
current_step INT NOT NULL
,current_retry_attempt INT NOT NULL
,job_state INT NOT NULL
) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
--Capture Jobs currently working
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
-- exec sp_whoisactive
--select * from @currently_running_jobs
--Temp table exists check
IF OBJECT_ID('tempdb..##RunningJobs') IS NOT NULL
DROP TABLE ##RunningJobs
CREATE TABLE ##RunningJobs (
[JobID] [UNIQUEIDENTIFIER] NOT NULL
,[JobName] [sysname] NOT NULL
,[StartExecutionDate] [DATETIME] NOT NULL
,[AvgDurationMin] [INT] NULL
,[DurationLimit] [INT] NULL
,[CurrentDuration] [INT] NULL )
-- truncate table ##RunningJobs
INSERT INTO ##RunningJobs (
JobID
,JobName
,StartExecutionDate
,AvgDurationMin
,DurationLimit
,CurrentDuration )
SELECT jobs.Job_ID AS JobID
,jobs.NAME AS JobName
,act.start_execution_date AS StartExecutionDate
,AVG(FLOOR(run_duration / 100)) AS AvgDurationMin
,CASE
--If job average less than 5 minutes then limit is avg+10 minutes
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
--If job average greater than 5 minutes then limit is avg*limit percentage
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END AS DurationLimit
,DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
FROM @currently_running_jobs crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
AND act.start_execution_date > (GETDATE() - 4)
INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
AND hist.step_id = 0
WHERE crj.job_state = 1 and crj.running = 1
GROUP BY jobs.job_ID
,jobs.NAME
,act.start_execution_date
,DATEDIFF(MI, act.start_execution_date, GETDATE())
HAVING CASE
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
WHEN jobs.NAME = 'Google Analytics Download' and DATEDIFF(MI, act.start_execution_date, GETDATE()) > 240 then 240
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END < DATEDIFF(MI, act.start_execution_date, GETDATE())
--Checks to see if a long running job has already been identified so you are not alerted multiple times,
-- as long as it is the same day. If it was already reported, but is still running the next day, then report it again.
IF EXISTS ( SELECT RJ.*
FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.LongRunningJobs )
or convert(varchar(8),RJ.StartExecutionDate,112) <= convert(varchar(8),getdate()-1,112) )
BEGIN
--Send email with results of long-running jobs
-- select * from LongRunningJobs order by RowInsertDate desc
SET @tableHTML = @BodyText +
N'<H1>Long Running SQL Jobs</H1>' +
N'<table border="1">' +
N'<tr><th>Job Name</th><th>Start_Time</th>' +
N'<th>Avg_Min</th><th>Curr_Min</th></tr>' +
--N'<th>DL_Table</th><th>Records</th></tr>' +
CAST ( ( select td = substring(RJ.JobName,1,35) ,' ',
td = cast(RJ.StartExecutionDate as char(23)), ' ',
td = cast(RJ.AvgDurationMin as char(7)) , ' ' ,
td = cast(RJ.CurrentDuration as char(7)), ' '
FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate)
NOT IN (Select CHECKSUM(JobID,StartExecutionDate)
From dbo.LongRunningJobs)
or convert(varchar(8),RJ.StartExecutionDate,112) <= convert(varchar(8),getdate()-1,112)
--order by j.name, run_time desc, step_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA_Alerts',
@recipients= 'MyEmail@Email.com',
@subject = 'Long Running SQL Job XML: Servername',
@body = @tableHTML,
@body_format = 'HTML',
@importance = 'High'
/* --============= OLD
EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile
,@recipients = @MailRecipients
,@query = 'USE DataMaint; Select substring(RJ.JobName,1,35),
RJ.StartExecutionDate as ''start_Time'', RJ.AvgDurationMin as ''Avg_Min'',
RJ.DurationLimit as ''Limit'' , RJ.CurrentDuration as ''Curr_Min''
From ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate) NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) '
,@body = 'View attachment to view long running jobs'
,@subject = 'Long Running SQL Job: ClientviewDB'
,@attach_query_result_as_file = 1;
-- =========== end OLD */
--Populate LongRunningJobs table with jobs exceeding established limits
INSERT INTO DataMaint.[dbo].[LongRunningJobs]
( [JobID]
,[JobName]
,[StartExecutionDate]
,[AvgDurationMin]
,[DurationLimit]
,[CurrentDuration], [RowInsertDate],Servername )
( SELECT RJ.*, GETDATE(), 'ServerName' FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate)
NOT IN (SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.LongRunningJobs ) )
-- select top 100 * from DataMaint.[dbo].[LongRunningJobs]
END
DROP TABLE ##RunningJobs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply