April 30, 2016 at 11:04 am
Hi TSQL Gurus,
Need small help.
Basically, looking of a query which will give me the number of times/count of times the job will run in a day based on the job schedule. For example, The job is scheduled to execute every 1 hour in a day, then my output should be 24 times.
How to write such a logic? Can anyone help?
My SQL Version is : SQL 2012 Enterprise Edition
Thanks in advance.
April 30, 2016 at 11:51 pm
Anybody ???
Repeating the requirement,
For example, The job is scheduled to execute every 1 hour in a day (Occurs every day every 1 hour(s) between 12:00:00 AM and 11:59:59 PM), then my output should show me Jobname and Execution count per day = 24 times.
May 1, 2016 at 12:35 am
Where are you getting the frequency information from? Care to share that part?
May 1, 2016 at 2:21 am
I pasted from GUI. Went to the Job properties -> Schedules.
Programmatically, we have to get it from msdb.dbo.sysschedules.
May 1, 2016 at 5:00 am
May 18, 2016 at 4:51 am
Hi All,
I was able to get some help and got the query. But it doesn't work if we select "Occurs once at" in the Schedule section of the job. However, If I select "Occurs every x mins, x hours" this query works well.
Checking if anybody can help me in fixing this.
Attaching Working.png & Networking.png screenshots.
SQL version : 2012
Query :
SELECT j.job_id, j.name AS jobname,
(CASE WHEN CAST(CAST(active_start_date AS varchar(10)) AS datetime) = CAST(GETDATE() AS date)
THEN 24-DATEDIFF(hh,0,TIMEFROMPARTS (CONVERT(CHAR(2), active_start_time/10000),CONVERT(CHAR(2), (active_start_time % 10000) / 100),CONVERT(CHAR(2), (active_start_time % 100)), 0, 0 ))
WHEN CAST(CAST(active_end_date AS varchar(10)) AS datetime) = CAST(GETDATE() AS date)
THEN DATEDIFF(hh,0,TIMEFROMPARTS (CONVERT(CHAR(2), active_end_time/10000),CONVERT(CHAR(2), (active_end_time % 10000) / 100),CONVERT(CHAR(2), (active_end_time % 100)), 0, 0 ))
ELSE
24
END * CASE s.freq_subday_type
WHEN 8 THEN 1
WHEN 4 THEN 60
WHEN 2 THEN 60 * 60
END)/freq_subday_interval AS ScheduledExecutionsPerDay
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobschedules js
ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysschedules s
ON s.schedule_id = js.schedule_id
WHERE s.enabled = 1 -- show me only jobs whose schedules are enabled
and j.name not like 'sys%'
AND s.freq_type in (4, 8) --- means Daily
AND j.enabled = 1 -- show me only enabled jobs
go
Thanks in advance.
May 19, 2016 at 8:44 am
Look at the condition 'AND s.freq_type in (4, 8) --- means Daily'.
If [freq_type] in msdb.dbo.sysschedules is 1, the [active_start_date]
and [active_start_time] give the details.
May 21, 2016 at 12:19 pm
Hi Doug,
So what should be my WHERE conditopn be? If I put a WHEERE Clause as freq_type = 1, no output is displayed.
May 23, 2016 at 4:44 pm
I hope this doesn't come over as rude but how is your sql? Most of the logic is there and I pointed you at the condition which was stopping the 'once only' jobs to run.
I see that your query is looking for jobs on the day that the query is run so you could take the query that Eirikur gave you and alter the logic so that instead of building the strings which display the schedule, you check for today's date.
If you want to, you could just use the query as it is and do a little manual work to find out if any of your jobs are due to run on the day you run the query. I suppose it depends what it is you are trying to do.
May 24, 2016 at 2:26 am
Okay thanks Doug.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply