Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to send alert when jobs runs more than 24 hours Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 5:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 31, Visits: 171
Can someone post the script which would send out an email notification if any job on a server runs longer than 24 hours?
Post #1413807
Posted Thursday, January 31, 2013 2:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 13,605, Visits: 10,480
I found this script when I entered your topic title into Google:

Identifying Long Running SQL Server Agent Jobs

Maybe you need to brush up your Google skills.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1413914
Posted Thursday, January 31, 2013 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 31, Visits: 171
But, how do I implement this?
Do I create job for this?
Post #1414010
Posted Thursday, January 31, 2013 5:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:01 AM
Points: 13,605, Visits: 10,480
Since you'll have to run it periodically, I suppose a job is the preferred method.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1414014
Posted Thursday, January 31, 2013 11:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:30 PM
Points: 54, Visits: 358
You can execute it every one hour and if found then send out an email
Post #1414266
Posted Thursday, January 31, 2013 5:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 31, Visits: 171
I am newbie on this. Do I just execute the stored procedure in sql job or whole script.
If you can walk me through the process will really appreciate it.
Post #1414403
Posted Thursday, January 31, 2013 11:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
pretea (1/31/2013)
Do I just execute the stored procedure in sql job or whole script.
.
better use "usp_long_running_jobs" SP in job as step.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1414451
Posted Friday, February 1, 2013 5:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:46 PM
Points: 31, Visits: 171
Thanks.

What I did was execute this function to create in msdb.
CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This function will take any binary value and return
-- the hex value as a character representation.
-- In order to use this function you need to pass the
-- binary hex value and the number of bytes you want to
-- convert.
begin

declare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0
set @h = ''
-- process all bytes
while @j < @l
begin
set @j= @j + 1
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
-- build string of hex characters
set @h = @h + @s
end
return(@h)
end


Then, create stored proce in msdb.
CREATE proc usp_long_running_jobs as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This stored procedure will detect long running jobs.
-- A long running job is defined as a job that has
-- been running over 6 hours. If it detects any long
-- running job then an email is sent to the DBA's.

------------------
-- Begin Section 1
------------------

set nocount on

declare @c char(1000)
declare @cnt int

-- Create table to hold job information
create table #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)

------------------
-- Begin Section 2
------------------

-- create a table to hold job_id and the job_id in hex character format
create table ##jobs (job_id uniqueidentifier ,
job_id_char varchar(100))

-- Get a list of jobs
insert into #enum_job
execute master.dbo.xp_sqlagent_enum_jobs 1,
'garbage' -- doesn't seem to matter what you put here

------------------
-- Begin Section 3
------------------

-- calculate the #jobs table with job_id's
-- and their hex character representation
insert into ##jobs
select job_id, dba.dbo.fn_hex_to_char(job_id,16) from #enum_job

------------------
-- Begin Section 4
------------------

-- get a count or long running jobs
select @cnt = count(*)
from master.dbo.sysprocesses a
join ##jobs b
on substring(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c on b.job_id = c.job_id
-- check for jobs that have been running longer that 6 hours.
where login_time < dateadd(hh,-6,getdate())

------------------
-- Begin Section 5
------------------

if @cnt > 0
-- Here are the long running jobs
exec master.dbo.xp_sendmail
@recipients='Greg.Larsen@databasejournal.com',
@subject='Jobs Running Over 6 hours',
@query= 'select substring(c.name,1,78)
''These jobs have been running longer than 6 hours''
from master.dbo.sysprocesses a
join ##jobs b
on substring(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c on b.job_id = c.job_id
where login_time < dateadd(hh,-6,getdate())'

drop table #enum_job
drop table ##jobs
GO


Then created job like you said and did exec usp_long_running_jobs to run and made it to run every 7 hours so if any job is runnning more than 6 hours it should send notification right? But, it doesnt send me any email.
Post #1414595
Posted Friday, February 1, 2013 7:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
Ironically, you're working on the wrong problem. You should be working on making the job take less than 24 hours. Peel one potato at a time by finding just one slow part and fixing it. Find the next slow part and fix it. Repeat until the code takes minutes instead of hours.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1414896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse