Improve naming convention for schedules

[petervandivier]

Presently - each task gets its own dedicated schedule with the same name as the task itself. This is helpful for small-volume deployments but as you scale up the human-readability value of this model decreases.

Most notably, when default job failure notifications come through - the name of the schedule confers no information about its frequency or interval start time.

A schedule name that communicates information about the schedule itself improves the information density of a job failure notification and allows the receiver to better take action.

msdb.dbo.sysschedules.name is of type sysname which limits us to 128 characters. Therefore if there's a desire to keep the 1:1 schedule→task mapping communicated also in the name, then it may be prudent to use sysjobs.job_id rather than sysjobs.name for this pointer to reduce risk of overflow

[taddison]

Do you have a suggestion for what to put in the schedule name?

The naming of schedules didn't have any particular motivation (aligning with task name was pretty much a default), so using it to capture something else isn't taking away from any existing requirement. When inspecting schedules on the server the mapping is explicitly captured by the schedule's link to the job, so the name losing that is ok.

[petervandivier]

How about a complexified version of "$($_.TaskUid)-$($_.Frequency)-$($_.FrequencyInterval)-$($_.StartTime)"

For task foo with the following definition...

{
    "TaskUid":  "deadbeef-deaf-beef-dead-beefdeadbeef",
    "Identifier":  "foo",
    "TSQLCommand":  "exec foo.bar.baz;",
    "StartTime":  "06:30:00",
    "Frequency":  "Day",
    "FrequencyInterval":  0,
    "NotifyOnFailureOperator":  "DBA",
    "IsNotifyOnfailure":  true,
    "IsEnabled":  true,
    "IsDeleted":  false,
    "NotifyLevelEventLog":  "OnFailure"
}

...those attributes (plus some judicious text massaging) might give us the schedule name deadbeefdeafbeefdeadbeefdeadbeef-daily-0-06300 as an initial value.

The TaskUid remains present in the name to uniquely id the task (but as a fixed-length guid we are safe from overflow). The interval, frequency, and starttime are embedded in the name as well which communicates more to the recipient of a job failure notification. Consider the difference between:

JOB RUN:    'foo' was run on 12/1/2021 at 06:30:00 AM
DURATION:   1 hours, 1 minutes, 1 seconds
STATUS:     Failed
- MESSAGES: The job failed.  The Job was invoked by Schedule 100 (foo).  The last step to run was step 1 (foo).
+ MESSAGES: The job failed.  The Job was invoked by Schedule 100 (deadbeefdeafbeefdeadbeefdeadbeef-daily-0-06300).  The last step to run was step 1 (foo).

This provides a greater information density more rapidly in a triage situation.

[taddison]

Looks good, and not even sure we need the TaskUid in the schedule name (so you could go wild pretty-printing the schedule description).