August 6, 2015 at 3:32 am
In my requirement,My production server running more then 5 jobs .
I want details in running schedule in minutes.For example
1) First Job
I am configured every one hour.
2) Second job
Configured in every 30 min
3) Third job
Configured every 60 min
4) Fourth Job
Configured every 24 hrs
I want the result
SNOJOBNameRunning Schedule(in Min)
1First JOB 60min
2Second JOB 30 min
3Third JOB 60min
4Fourth JOB 1440min
August 6, 2015 at 9:43 am
Well there are many different combinations a schedule can make so you have to be mindful of what exactly you want.
Use this as a reference if you want to make any changes.
https://msdn.microsoft.com/en-us/library/ms178644(v=sql.90).aspx
In the following example I'm only looking at Daily Jobs that run every x hours OR x minutes.
USE [msdb]
SELECT
sj.name AS JOBName,
--ss.freq_subday_type,
--ss.freq_subday_interval,
CASE WHEN ss.freq_subday_type = 4 THEN CAST(ss.freq_subday_interval AS VARCHAR(10)) + ' min'
ELSE CAST((ss.freq_subday_interval*60) AS VARCHAR(10)) + ' min' END AS 'Running Schedule(in Min)'
FROM
dbo.sysjobs sj
JOIN dbo.sysjobschedules sjs ON sjs.job_id = sj.job_id
JOIN dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE
sj.enabled = 1
AND ss.freq_type = 4
AND ss.freq_interval = 1
AND (ss.freq_subday_type = 4 OR ss.freq_subday_type = 8)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply