January 30, 2013 at 5:46 pm
Can someone post the script which would send out an email notification if any job on a server runs longer than 24 hours?
January 31, 2013 at 2:09 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2013 at 5:12 am
But, how do I implement this?
Do I create job for this?
January 31, 2013 at 5:19 am
Since you'll have to run it periodically, I suppose a job is the preferred method.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2013 at 11:26 am
You can execute it every one hour and if found then send out an email
January 31, 2013 at 5:09 pm
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.
January 31, 2013 at 11:33 pm
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;-)
February 1, 2013 at 5:31 am
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-2 varchar(100)
declare @h varchar(100)
declare @j-2 int
set @j-2 = 0
set @h = ''
-- process all bytes
while @j-2 < @l
begin
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s-2 = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s-2 = cast(substring(@digits,@i%16+1,1) as char(1)) + @s-2
-- build string of hex characters
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.
February 1, 2013 at 7:37 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply