Neil Hambly

Too many Schedules

This blog is long overdue, I've had many an SQL related idea that could/should have made itself into a blog but has yet to happen.

So this is the going to be the first of the blogs I'm planning to post in next few weeks, so which topic is this one I have chosen today,

We'll try as I might to come up with a catchy title I must defeat as the Title clearly indicates.

Too many cooks' schedules ....spoil the broth MDSB database

So to be less cryptic, if you have anything like a typical environment, you will likely have some SQL instances

Which have many-many (this may even number in the hundreds) of SQL Jobs, I also suspect that a large proportion

Of these Jobs do have their very own schedules

So when we examine these what are the chances that some they have identical schedules?

(I'm thinking it is more than 50-50 chance), let's look @ an example to illustrate the point in case

Two example jobs (each with its own schedule - these are identical)

SQL Job ("Job 12")   executes @ on the following schedule: Every Weekday (Mon-Fri) on the hour

SQL Job ("Job 256") executes @ on the following schedule: Every Weekday (Mon-Fri) on the hour

So clearly this type of occurrence can and does regularly occur, with 2 of more different Jobs each running on identical schedules,

but having their own schedule, now if it was simply that this occurred to only a few schedules here and there, it's unlikely I would be writing this blog entry,

And sure some of those identical schedules may indeed even warrant their very own "dedicated" schedules,

But for the main I would speculate that a large percentage could and most IMHO should be using a "Shared" schedule as

This would be more advantageous. Why?

Well my reasoning is along the lines of being able to visualizing my overall Job Schedules,

Identifying when & which jobs ran @ a specific time or during a certain time range,

Aiding in troubleshooting, helping with capacity planning and other workload based analysis.....

Hopefully you getting the picture by now that using shared schedules can be beneficial IMHO.

So in an attempt to identify these potential schedules that are "grouped" I have coded a T-SQL script (listed below) to provide me with a report.

What is the script {attempting} to do - simply it is reporting on the existing schedules. With the following

1.        Group all related Schedules - See GrpNumber column

2.        Order by Schedule_id (first for each group) this to be the one to migrate the other's in for that group.

3.        Identify (see instruction column) on what Actions should be taken#

4.        Once reviewed the Instructions (choosing which to implement if so desired) to move certain jobs to other

           Schedules is simply executing the T-SQL command sp_attach_schedule         

           EXEC sp_attach_schedule @job_name = N'<Job Name>',@schedule_name = N'<schedule name>';

5.        Remove any schedule(s) that are now longer required.

           EXEC sp_delete_schedule (Arguments)             

Looking @ an Example:

In this example we have a group #5 with 2 schedules {id's 9 & 11}

With the 1^st schedule_id of these 2 (in ascending order) being schedule_id {9} naturally

So in this case we are using schedule_id {9} as the one we are retaining and migrating the other schedule_id(s)

for the group to this schedule, hence the instruction's "Keep This" for schedule_id {9} and schedule_id {11}

the instruction "Moving This to Schedule_id:9"

Instruction                              schedule_id  GrpNumber RowNumber     <other columns omitted>

Keep This                               9                   5                       1

Moving This to Schedule_id:9   11                  5                       2

So maybe look @ the schedule's you have on your servers and see if any "grouping" of schedules is possible

and then decide if this any sharing is appropriate to your environment - as always "IT DEPENDS" on your situation

WORD OF CAUTION: please make sure you fully understand want you are doing, and have backups in place prior to performing any actions..

But with that said I hope this can be of some benefit.

USE MDSB

GO

OVER(Partition by

,freq_relative_interval,freq_recurrence_factor,active_start_time

schedule_id,freq_type,freq_interval,freq_subday_type,freq_subday_interval,

) AS 'RowNumber'

,freq_type

,freq_subday_type

,freq_relative_interval

,active_start_time

--and schedule_id > 12 --- replication ones..Best we leave these as is ...

SELECT *

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Keeps

,freq_relative_interval,freq_recurrence_factor,active_start_time)

UNION

,Moves.schedule_id

,Keeps.freq_relative_interval,Keeps.freq_recurrence_factor,Keeps.active_start_time

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Keeps

,freq_relative_interval,freq_recurrence_factor,active_start_time)

INNER JOIN

(

SELECT ROW_NUMBER() OVER(order by rownumber) AS 'GrpNumber'

,freq_relative_interval,freq_recurrence_factor,active_start_time

) AS Moves

,freq_relative_interval,freq_recurrence_factor,active_start_time)

AND Keeps.freq_interval = Moves.freq_interval

AND Keeps.freq_subday_interval = Moves.freq_subday_interval

AND Keeps.active_start_time = Moves.active_start_time

,CTE1.schedule_id,CTE1.GrpNumber,CTE1.RowNumber

,CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))

,dbo.udf_schedule_description

(

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

FROM dbo.sysjobs

INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

RIGHT OUTER JOIN #Schedules_CTE CTE1 ON dbo.sysjobschedules.schedule_id = CTE1.schedule_id

WHERE CTE1.RowNumber = 1

+ (Select TOP 1 CONVERT(VARCHAR(20),CTE3.schedule_id)) AS Instruction

,dbo.sysjobs.name

+ ':'

,dbo.udf_schedule_description

(

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

,dbo.sysjobs.enabled

,dbo.sysschedules.freq_interval

,dbo.sysschedules.freq_subday_interval

,dbo.sysschedules.freq_recurrence_factor

,dbo.sysschedules.active_end_date

,dbo.sysschedules.active_end_time

FROM dbo.sysjobs

INNER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

INNER JOIN #Schedules_CTE CTE2 ON dbo.sysjobschedules.schedule_id = CTE2.schedule_id

right outer JOIN #Schedules_CTE CTE3 ON CTE2.GrpNumber = CTE3.GrpNumber AND CTE3.RowNumber = 1

WHERE CTE2.RowNumber > 1

order by 3 ASC, 4 ASC, 6 ASC

DROP table #Schedules_CTE