August 30, 2011 at 2:48 pm
I have been trying to come up with a script that detects long-running jobs (over a specified threshold) and sends out an email when that threshold is exceeded.
Well, unless I am missing something (quite possible), this was quite painful to do.
First, I discovered that querying sys.dm_exec_requests and sys.dm_exec_sessions was giving me only information on the current job step.
For example, if a multi-step job is currently running step 15, the following query will give info on the start time of step 15 and the duration of step 15 only!
SELECT
r.session_id
,r.start_time
,r.total_elapsed_time
FROM
sys.dm_exec_sessions s
LEFT OUTER JOIN
sys.dm_exec_requests r
ON
s.session_id = r.session_id
WHERE
s.[program_name] LIKE 'SQLAgent - TSQL JobStep %'
I then had to query msdb.dbo.sysjobhistory and msdb.dbo.sysjobs to get at the total duration of steps completed so far in a currently executing job (http://www.sqlservercentral.com/articles/Administration/howtofindthatjobisrunning/2071/).
Here is my (clumsy) attempt at this:
DECLARE @completed AS TABLE
(
job SYSNAME
,run_duration_hrs SMALLINT
,run_duration_min SMALLINT
,run_duration_sec SMALLINT
,run_duration INT
);
INSERT INTO @completed
(
job
,run_duration_hrs
,run_duration_min
,run_duration_sec
,run_duration
)
SELECT
sj.name
,run_duration_hrs = CASE WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) < 5 THEN 0
WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) = 5 THEN LEFT ( CAST(sjh.run_duration AS NVARCHAR), 1)
ELSE LEFT ( CAST(sjh.run_duration AS NVARCHAR), 2)
END
,run_duration_min = CASE WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) < 3 THEN 0
WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) = 3 THEN LEFT ( CAST(sjh.run_duration AS NVARCHAR), 1)
WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) = 4 THEN LEFT ( CAST(sjh.run_duration AS NVARCHAR), 2)
ELSE LEFT (RIGHT ( CAST(sjh.run_duration AS NVARCHAR), 4), 2)
END
,run_duration_sec = CASE WHEN LEN( CAST(sjh.run_duration AS NVARCHAR) ) > 1 THEN RIGHT ( CAST(sjh.run_duration AS NVARCHAR), 2)
ELSE sjh.run_duration
END
,sjh.run_duration
FROM
msdb.dbo.sysjobhistory sjh
INNER JOIN msdb.dbo.sysjobs sj
ON
sj.job_id = sjh.job_id
WHERE
sjh.instance_id >
( SELECT MAX(instance_id)
FROM msdb.dbo.sysjobhistory sjh1
WHERE sjh1.step_name = '(Job outcome)'
AND sjh1.job_id = sjh.job_id )
ORDER BY
sj.name
,sjh.step_id ;
SELECT
c.job
,SUM( (c.run_duration_hrs * 3600) + (c.run_duration_min * 60) + c.run_duration_sec ) * 1000 AS total_completed_steps_duration_ms
FROM @completed c
GROUP BY
c.job;
Finally I had to combine the results from the two outputs above to get the total duration of currently executing jobs.
To make matters worse, the run_duration column in sysjobhistory is INT data type of format "HHMMSS", which explains the hoops I had to jump through in my 2nd script above.
Anyone have a better way to get a list of currently executing jobs sorted by total duration?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 3:51 am
tricky, the problem with "long running" is define what is actually long running, it sort of means you've got to compare back to previous runs to decide if the current run is actually longer than "normal".
I'm sure you can achieve what you want, I'll have a look. Idera Diagnostic Manager does this for me, although I usually end up turning the alert off as I have many jobs which run different times over a day.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 31, 2011 at 3:56 am
oh yes the "times" - back in sql 2000 I wrote some functions which convert the times into something usable.
USE [msdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Int2Duration]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_Int2Duration]
GO
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_Int2Duration] (@p1 integer)
-- =============================================================
-- Function: fn_Int2Duration
-- Written by: (c) Colin Leversuch-Roberts
-- http://www.kelemconsulting.co.uk
--
-- Purpose: to convert the ms int time storage in sysjobhistory into something readable
--
-- System:DBA maintenance
--
-- Input Paramters: @p1 integer ( see notes )
--
-- Returns : integer( see notes )
--
-- Usage: select dbo.fn_Int2Duration(@p1)
--
-- Notes:function to convert the ms int time storage into something readable
-- this is for the duration of jobs and returns the result in minutes
-- Seconds are rounded and where the time is less than 30 secs 1 min is returned.
--Duration is stored as hhmmss in an integer e.g. 1 hour = 10000, 1 min 45 sec= 145
--
-- VERSION HISTORY
-- Version NoDateDescription
-- 104-November-2000Initial Release
--
-- =============================================================
returns int
WITH SCHEMABINDING
begin
declare @ctime varchar(6),@len tinyint,@finaltime int
set @ctime=convert(varchar(6),@p1)
set @len=len(@ctime)
IF @len=1
set @finaltime=1-- less than 10 secs return 1 minute
IF @len=2
set @finaltime=1-- less than 60 secs return 1 minute
IF @len=3
set @finaltime=convert(tinyint,left(@ctime,1))+ convert(tinyint,round(convert(float,right(@ctime,2)/60.0),0))
IF @len=4
set @finaltime=convert(tinyint,left(@ctime,2))+ convert(tinyint,round(convert(float,right(@ctime,2)/60.0),0))
IF @len=5
set @finaltime=convert(int,left(@ctime,1))*60+convert(tinyint,substring(@ctime,2,2))+ convert(tinyint,round(convert(float,right(@ctime,2)/60.0),0))
IF @len=6
set @finaltime=convert(int,left(@ctime,1))*60+convert(tinyint,substring(@ctime,3,2))+ convert(tinyint,round(convert(float,right(@ctime,2)/60.0),0))
return(@finaltime) -- minutes
end
GO
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 31, 2011 at 7:21 am
colin.Leversuch-Roberts (8/31/2011)
oh yes the "times" - back in sql 2000 I wrote some functions which convert the times into something usable.
Thanks for sharing. By threshold I mean a certain time length, beyond which an alert would be sent. In my case that is 5 hrs.
I was certainly surprised by how tricky it was to get the overall time a job is currently running.
I was expecting to be able to get that from one place, not two.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 1, 2011 at 8:51 am
yup working with msdb is a bit of a black art at times. Can't think the number of times I've thought " why on earth did they do that?" I suspect msdb has had the least changes since sql 6.x or maybe before.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 1, 2011 at 8:32 pm
Hi,
I developed the following SP to stop long running jobs and I am using it for a while with no issues:
--********************************************
--Procedure to Stop Long running jobs
--Usage: exec dbo.usp_stop_long_running_jobs 2, '[0-9]%', 'marat@server.com'
--Developed by Anatoliy Martirosian 16.08.2010
--v2 notification added
--********************************************
CREATE PROCEDURE [dbo].[usp_stop_long_running_jobs]
@timeout int = 2 --timeout in hours
,@filter nvarchar(80) = '[0-9]%' --job filter
,@notify nvarchar(400) = 'marat@server.com'
AS
SET NOCOUNT ON
declare @Job nvarchar(1024), @sql nvarchar(2000), @sbj nvarchar(80)
create table ##job_activity (
session_id int,
job_id uniqueidentifier,
job_name sysname, --nvarchar(1024),
run_requested_Date datetime,
run_requested_source int,
queued_date datetime,
start_execution_date datetime,
last_executed_step_id int,
last_exectuted_step_date datetime,
stop_execution_date datetime,
next_scheduled_run_date datetime,
job_history_id int,
[message] nvarchar(1024),
run_status int,
operator_id_emailed int,
operator_id_netsent int,
operator_id_paged int
)
insert into ##job_activity
exec msdb.dbo.sp_help_jobactivity
if exists (
select 1 from ##job_activity
where start_execution_date is not Null and stop_execution_date is Null
and datediff(hour, start_execution_date, getdate()) > @timeout
and job_name like @filter
)
begin
--print 'There are Long running jobs'
declare Job_Cursor cursor for
select job_name from ##job_activity
where start_execution_date is not Null and stop_execution_date is Null
and datediff(hour, start_execution_date, getdate()) > @timeout
and job_name like @filter
open Job_Cursor
fetch next from Job_Cursor into @Job
while @@FETCH_STATUS = 0
begin
set @sql = 'exec msdb.dbo.sp_stop_job N''' + @Job + ''''
-- print @sql
exec (@sql)
fetch next from Job_Cursor into @Job
end
close Job_Cursor
deallocate Job_Cursor
set @sql = 'select job_name from ##job_activity
where start_execution_date is not Null and stop_execution_date is Null
and datediff(hour, start_execution_date, getdate()) > ' + cast(@timeout as varchar(8)) +
' and job_name like ''' + @filter + ''''
set @sbj = 'The following Long running jobs have been stopped on ' + @@SERVERNAME
exec msdb.dbo.sp_send_dbmail
@recipients = @notify
,@subject = @sbj
,@query = @sql
end
drop table ##job_activity
GO
October 4, 2011 at 10:14 am
marat-oz (9/1/2011)
Hi,I developed the following SP to stop long running jobs and I am using it for a while with no issues:
...
Thanks for sharing; I tried it and, indeed, your script works well.
In my case I wanted to also get information on currently executing requests (SQL statement and batch running, query plan etc.), hence the need to query the dm_exec_xxx DMVs.
BTW, there seems to be a bug in sp_help_jobactivity ;
the following command returns the same value of session_id for all sessions:
exec msdb.dbo.sp_help_jobactivity
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 4, 2011 at 2:03 pm
Here is my code for this (attached).
Run script "CurrentlyRunningJobs_GetTotalDurationCompletedSteps.sql.txt" in a DBA-owned database on the instance in question, say UTILDB, to create stored procedure [CurrentlyRunningJobs_GetTotalDurationCompletedSteps].
Then run script "CurrentlyRunningJobs.sql.txt" to get the currently-running jobs (and job steps) sorted by total elapsed time and showing the currently executed SQL batch and SQL statements.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply