June 23, 2009 at 10:42 am
..................................
June 23, 2009 at 10:55 am
June 23, 2009 at 11:31 am
.........................
June 23, 2009 at 11:35 am
we use this procedure for tracking long running jobs and sending out emails once it finds one:
"All credits to the Author of the script: I forgot who that wonderful person was"
set nocount on
declare @c char(1000)
declare @cnt int
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
)
create table ##jobs (job_id uniqueidentifier , job_id_char varchar(100))
insert into #enum_job
execute master.dbo.xp_sqlagent_enum_jobs 1,'garbage'
insert into ##jobs
select job_id, dbo.fn_hex_to_char(job_id,16) from #enum_job
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
where login_time 0
exec msdb..sp_send_dbmail
@recipients='sss.sss.com',
@subject='Jobs Running Over 1 hour(s) on servername',
@query= 'select substring(c.name,1,78)
''These jobs have been running longer than 1 hour(s)''
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,-1,getdate())'
drop table #enum_job
drop table ##jobs
Maninder
www.dbanation.com
June 23, 2009 at 11:51 am
...................
June 23, 2009 at 12:00 pm
Mani Singh (6/23/2009)
we use this procedure for tracking long running jobs and sending out emails once it finds one:"All credits to the Author of the script: I forgot who that wonderful person was"
set nocount on
declare @c char(1000)
declare @cnt int
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
)
create table ##jobs (job_id uniqueidentifier , job_id_char varchar(100))
insert into #enum_job
execute master.dbo.xp_sqlagent_enum_jobs 1,'garbage'
insert into ##jobs
select job_id, dbo.fn_hex_to_char(job_id,16) from #enum_job
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
where login_time 0
exec msdb..sp_send_dbmail
@recipients='sss.sss.com',
@subject='Jobs Running Over 1 hour(s) on servername',
@query= 'select substring(c.name,1,78)
''These jobs have been running longer than 1 hour(s)''
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,-1,getdate())'
drop table #enum_job
drop table ##jobs
this sp will work in 2000.. you might be getting the error on db_mail whch is not supported by 2000. insted of exec msdb..sp_send_dbmail use exec master.dbo.xp_sendmail
Maninder
www.dbanation.com
June 23, 2009 at 12:51 pm
...........
June 23, 2009 at 12:55 pm
..........
June 23, 2009 at 1:07 pm
......................
June 23, 2009 at 1:13 pm
can you post the results of:
(sqlserver version info)
Select @@version
In the SSC article Mani Singh posted in the first the scripts are very visible to me.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 23, 2009 at 1:18 pm
.................
June 23, 2009 at 1:22 pm
Guy, you should really start researching problems by your own...! In my opinion you try to let us make your job.
@all the other guys and gals see this:
http://www.sqlservercentral.com/Forums/Topic740223-145-1.aspx
June 23, 2009 at 2:27 pm
kiranjanga2 (6/23/2009)
i am getting the error as (Invalid object name 'dbo.fn_hex_to_char'.)
kiranjanga2,
I've found the solution for your issue. However, since we're doing your work for you, it's only fair that we get compensated for it. Please contact me to arrange payment via paypal, then I'll provide the solution to you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply