Obtaining statistical data in MSDB db

Dear folks,
I would like have available statistical data concerning our scheduled DTS.
Up to date I am be able to store and to see some data using sysjobs and
sysjobhistory tables in MSDB database, i.e, I obtain variables such as last
execution, time inverted and owner, as well as owner or version id. That's
fine and very useful but I would need more accurate information in order to
create statistics with the total of time inverted per each DTS during the
last two ws (for instance) or be able to desing a "production map" and
therefore, to schedule better our DTS, covering spaces in terms of time and
avoiding bottlenecks or issues which sometimes are causing headaches to all
the developers.
How could I do such thing? I am seeing these aforementioned tables but the
info stored is the last day.
select run_date,run_duration,run_status,step_na
me from sysjobhistory where
step_name
in('__ABS_PasarDiarioaHistorico')
I see this:
20050805 2855 1 __ABS_PasarDiarioaHistor
ico
20050806 2518 1 __ABS_PasarDiarioaHistor
ico
20050807 148 1 __ABS_PasarDiarioaHistori
co
20050808 6 1 __ABS_PasarDiarioaHistorico
20050809 2113 1 __ABS_PasarDiarioaHistor
ico
20050810 3205 1 __ABS_PasarDiarioaHistor
ico
20050811 3355 1 __ABS_PasarDiarioaHistor
ico
But, July executions where on earth are?
Any though/comment/advice will be welcomed.
Regards,I've done this query which is solving any doubts:
SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
when 4 then 'Daily'
when 8 then 'Wly'
when 16 then 'Monthly'
when 32 then 'once'
end
FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
*** __ABS_EliminarHistorico is one of our DTS.
"Enric" wrote:

> Dear folks,
> I would like have available statistical data concerning our scheduled DTS.
> Up to date I am be able to store and to see some data using sysjobs and
> sysjobhistory tables in MSDB database, i.e, I obtain variables such as las
t
> execution, time inverted and owner, as well as owner or version id. That's
> fine and very useful but I would need more accurate information in order t
o
> create statistics with the total of time inverted per each DTS during the
> last two ws (for instance) or be able to desing a "production map" and
> therefore, to schedule better our DTS, covering spaces in terms of time an
d
> avoiding bottlenecks or issues which sometimes are causing headaches to al
l
> the developers.
> How could I do such thing? I am seeing these aforementioned tables but the
> info stored is the last day.
> select run_date,run_duration,run_status,step_na
me from sysjobhistory where
> step_name
> in('__ABS_PasarDiarioaHistorico')
> I see this:
> 20050805 2855 1 __ABS_PasarDiarioaHistor
ico
> 20050806 2518 1 __ABS_PasarDiarioaHistor
ico
> 20050807 148 1 __ABS_PasarDiarioaHistori
co
> 20050808 6 1 __ABS_PasarDiarioaHistorico
> 20050809 2113 1 __ABS_PasarDiarioaHistor
ico
> 20050810 3205 1 __ABS_PasarDiarioaHistor
ico
> 20050811 3355 1 __ABS_PasarDiarioaHistor
ico
>
> But, July executions where on earth are?
>
> Any though/comment/advice will be welcomed.
> Regards,|||Enric
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, wly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For wly, add in the days of the w
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly
on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'W day'
when 10 then 'Wend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes or
hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
> I've done this query which is solving any doubts:
> SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
> when 4 then 'Daily'
> when 8 then 'Wly'
> when 16 then 'Monthly'
> when 32 then 'once'
> end
> FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
> on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
> WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
> *** __ABS_EliminarHistorico is one of our DTS.
> "Enric" wrote:
>|||Thanks so much Uri, it's amazing, just a quick and powerful response.
"Uri Dimant" wrote:

> Enric
> select convert(varchar(22), j.name) as job_name
> , case freq_type -- Daily, wly, Monthly
> when 1 then 'Once'
> when 4 then 'Daily'
> when 8 then 'Wk ' -- For wly, add in the days of the w
> + case freq_interval & 2 when 2 then 'M' else '' end -- Monday
> + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
> + case freq_interval & 8 when 8 then 'W' else '' end -- etc
> + case freq_interval & 16 when 16 then 'Th' else '' end
> + case freq_interval & 32 when 32 then 'F' else '' end
> + case freq_interval & 64 when 64 then 'Sa' else '' end
> + case freq_interval & 1 when 1 then 'Su' else '' end
> when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Month
ly
> on a particular day
> when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
> the month" for example
> + case freq_relative_interval
> when 1 then 'Every First '
> when 2 then 'Every Second '
> when 4 then 'Every Third '
> when 8 then 'Every Fourth '
> when 16 then 'Every Last '
> end
> + case freq_interval
> when 1 then 'Sunday'
> when 2 then 'Monday'
> when 3 then 'Tuesday'
> when 4 then 'Wednesday'
> when 5 then 'Thursday'
> when 6 then 'Friday'
> when 7 then 'Saturday'
> when 8 then 'Day'
> when 9 then 'W day'
> when 10 then 'Wend day'
> end
> when 64 then 'Startup' -- When SQL Server starts
> when 128 then 'Idle' -- Whenever SQL Server gets bored
> else 'Err' -- This should never happen
> end as schedule
> , case freq_subday_type -- FOr when a job funs every few seconds, minutes
or
> hours
> when 1 then 'Runs once at:'
> when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> seconds'
> when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> minutes'
> when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours
'
> end as frequency
> -- All the subsrings are because the times are stored as an integer with n
o
> leading zeroes
> -- i.e. 0 means midnight, 13000 means half past one in the morning
> (01:30:00)
> , substring (right (stuff (' ', 1, 1, '000000') +
> convert(varchar(6),active_start_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time
),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time)
,
> 6) ,5 ,2) as start_at
> ,case freq_subday_type
> when 1 then NULL -- Ignore the end time if not a recurring job
> else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
> active_end_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,5 ,2) end as end_at
> from sysjobs j
> join sysJobSchedules s
> on j.job_id = s.job_id
> order by j.name, start_at
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
>
>