June 29, 2009 at 2:12 am
Hi All,
I need a script which should take a integer as input parameter(time in minutes), and should query sysjobs, sysjobhistory to check whether there is a mintanence window possible on the coming weekend where none of the below things are happening.
1. No job is scheduled to run.
2. No job will be running and the basis for this should be the average run time for last one or two months.
3. And if not then the out put should be the next biggest window in terems of time gap. e.g If I give 120 as parameter and its not there and the highest is say 70 mins then should return the time slot available like 3:10 AM to 4:20 AM etc.
I hope I'm clear about the requirment. Kindly let me know if you guys need any more input on this.
June 29, 2009 at 2:34 am
Hi Rohit,
Ramji (6/29/2009)
Kindly let me know if you guys need any more input on this.Rohit
It looks like you've misunderstood the concept of this site... We're not "free-of-charge-online-code-generators"!
The purpose of this forum is to help you not to substitute you.
Do we need any more input? Yes: Please show us, what you've tried so far and where you get stuck. By doing so you help us help you. For details on how to post questions please follow the link my signature.
As a side node: Your 2nd requirement cannot be met since the duration of a job cannot be predicted. Guessed, yes, but predicted, no. Example:How would somebody know if there isn't any code snippet with a conditional WAIT clause?
June 29, 2009 at 4:36 am
Ramji (6/29/2009)
Hi All,I need a script which should take a integer as input parameter(time in minutes), and should query sysjobs, sysjobhistory to check whether there is a mintanence window possible on the coming weekend where none of the below things are happening.
Have you checked the script library here (see link on left). There's very likely a script that you can adapt to your requirements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2009 at 5:48 am
lmu92 (6/29/2009)
Hi Rohit,Ramji (6/29/2009)
Kindly let me know if you guys need any more input on this.Rohit
It looks like you've misunderstood the concept of this site... We're not "free-of-charge-online-code-generators"!
The purpose of this forum is to help you not to substitute you.
Lutz thanks for your prompt response.... I didn't needed the ready made code. I've written below code.
--Creating a temp table to store whole information
drop table tempdb..[dba_job_sched]
create table tempdb..[dba_job_sched]
(
row_id int identity,
job_id char(40),
job_name varchar(250),
job_status smallint,
sched_id int,
sched_status smallint,
next_run_start char(8) null,
max_run_time char(8) null,
avg_run_time char(8) null
)
-- Inserting records for all the jobs which are scheduled
INSERT INTO
insert into tempdb..[dba_job_sched] (job_id, job_name, job_status, sched_id, sched_status)
SELECT J.JOB_ID, J.NAME, J.ENABLED, S.SCHEDULE_ID, S.ENABLED
FROM msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobschedules S ON J.JOB_ID = S.JOB_ID
WHERE S.ENABLED = 1
Now I'm facing problem in calculating average run time I've written below code but its not giving clear picture.
SELECT H.JOB_ID,
CASE len(avg(h.run_duration))
WHEN 1 THEN cast('00:00:0' + cast(avg(h.run_duration)as char) as char (8))
WHEN 2 THEN cast('00:00:' + cast(avg(h.run_duration)as char) as char (8))
WHEN 3 THEN cast('00:0' + Left(right(avg(h.run_duration),3),1)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 4 THEN cast('00:' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 5 THEN cast('0' + Left(right(avg(h.run_duration),5),1)
+ ':' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 6 THEN cast(Left(right(avg(h.run_duration),6),2)
+ ':' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
END RUN_TIME, COUNT(*) AS COUNTER
FROM msdb.dbo.sysjobhistory H
where JOB_ID = '7452003C-4D94-4A8C-9F57-BA64C8DFC1C4'
AND MESSAGE NOT LIKE '%Failed%'
GROUP BY JOB_ID
I just cross verify this code for one job and found below defects.
1. I manually calculated the average time and found difference
2. Even if I'm including the jobs which are not failed then also for some of the job history which says 0 duration but it is including it.
June 29, 2009 at 5:50 am
lmu92 (6/29/2009)
Hi Rohit,Ramji (6/29/2009)
Kindly let me know if you guys need any more input on this.Rohit
It looks like you've misunderstood the concept of this site... We're not "free-of-charge-online-code-generators"!
The purpose of this forum is to help you not to substitute you.
Lutz thanks for your prompt response.... I didn't needed the ready made code. I've written below code.
--Creating a temp table to store whole information
drop table tempdb..[dba_job_sched]
create table tempdb..[dba_job_sched]
(
row_id int identity,
job_id char(40),
job_name varchar(250),
job_status smallint,
sched_id int,
sched_status smallint,
next_run_start char(8) null,
max_run_time char(8) null,
avg_run_time char(8) null
)
-- Inserting records for all the jobs which are scheduled
INSERT INTO
insert into tempdb..[dba_job_sched] (job_id, job_name, job_status, sched_id, sched_status)
SELECT J.JOB_ID, J.NAME, J.ENABLED, S.SCHEDULE_ID, S.ENABLED
FROM msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobschedules S ON J.JOB_ID = S.JOB_ID
WHERE S.ENABLED = 1
Now I'm facing problem in calculating average run time as the format in syshobhistory for run_duration is HHMMSS. I'e written below code but its not giving clear picture.
SELECT H.JOB_ID,
CASE len(avg(h.run_duration))
WHEN 1 THEN cast('00:00:0' + cast(avg(h.run_duration)as char) as char (8))
WHEN 2 THEN cast('00:00:' + cast(avg(h.run_duration)as char) as char (8))
WHEN 3 THEN cast('00:0' + Left(right(avg(h.run_duration),3),1)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 4 THEN cast('00:' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 5 THEN cast('0' + Left(right(avg(h.run_duration),5),1)
+ ':' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
WHEN 6 THEN cast(Left(right(avg(h.run_duration),6),2)
+ ':' + Left(right(avg(h.run_duration),4),2)
+ ':' + right(avg(h.run_duration),2) as char (8))
END RUN_TIME, COUNT(*) AS COUNTER
FROM msdb.dbo.sysjobhistory H
where JOB_ID = '7452003C-4D94-4A8C-9F57-BA64C8DFC1C4'
AND MESSAGE NOT LIKE '%Failed%'
GROUP BY JOB_ID
I just cross verify this code for one job and found below defects.
1. I manually calculated the average time and found difference
2. Even if I'm including the jobs which are not failed then also for some of the job history which says 0 duration but it is including it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply