Script to send alert when jobs runs more than 24 hours

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Script to send alert when jobs runs more than...

Script to send alert when jobs runs more than 24 hours

Rate Topic

Display Mode

Topic Options

Author

Message

pretea

Posted Wednesday, January 30, 2013 5:46 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:57 PM
Points: 12, Visits: 94

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

Koen Verbeeck

Posted Thursday, January 31, 2013 2:09 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

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

pretea

Posted Thursday, January 31, 2013 5:12 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:57 PM
Points: 12, Visits: 94

But, how do I implement this?
Do I create job for this?

Post #1414010

Koen Verbeeck

Posted Thursday, January 31, 2013 5:19 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364, Visits: 6,462

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

sqlbi.vvamsi

Posted Thursday, January 31, 2013 11:26 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 48, Visits: 268

You can execute it every one hour and if found then send out an email

Post #1414266

pretea

Posted Thursday, January 31, 2013 5:09 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:57 PM
Points: 12, Visits: 94

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

Bhuvnesh

Posted Thursday, January 31, 2013 11:33 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1414451

pretea

Posted Friday, February 01, 2013 5:31 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:57 PM
Points: 12, Visits: 94

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

Jeff Moden

Posted Friday, February 01, 2013 7:37 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

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. Wink

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1414896

« Prev Topic | Next Topic »

Permissions