Ola Jobs

[cabana1]

My Ola jobs fail because I use different job names.

I like to group my jobs by name (I know I can give them 'categories'...but there isn't a great way to group like jobs)

Can the names be made dynamic in MaintenanceSolution.Tests.ps1?

$jobnames = @() $jobnames += [pscustomobject]@{JobName='DatabaseBackup - SYSTEM_DATABASES - FULL'; prefix='SystemFull'} $jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - FULL'; prefix='UserFull'} $jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - DIFF'; prefix='UserDiff'} $jobnames += [pscustomobject]@{JobName='DatabaseBackup - USER_DATABASES - LOG'; prefix='UserLog'} $jobnames += [pscustomobject]@{JobName='CommandLog Cleanup'; prefix='CommandLog'} $jobnames += [pscustomobject]@{JobName='DatabaseIntegrityCheck - SYSTEM_DATABASES'; prefix='SystemIntegrityCheck'} $jobnames += [pscustomobject]@{JobName='DatabaseIntegrityCheck - USER_DATABASES'; prefix='UserIntegrityCheck'} $jobnames += [pscustomobject]@{JobName='IndexOptimize - USER_DATABASES'; prefix='UserIndexOptimize'} $jobnames += [pscustomobject]@{JobName='Output File Cleanup'; prefix='OutputFileCleanup'} $jobnames += [pscustomobject]@{JobName='sp_delete_backuphistory'; prefix='DeleteBackupHistory'} $jobnames += [pscustomobject]@{JobName='sp_purge_jobhistory'; prefix='PurgeJobHistory'}

[SQLDBAWithABeard]

I think an option for this would be to copy the ola test. Add your unique names for your jobs save as a .Tests.ps1 files and include that test in the external repos.

[martin-guth]

Another approach I could think of could be to validate the entries in Ola's CommandLog Table. This would be independent from the method how the maintenance is invoked.

[SQLDBAWithABeard]

I like that idea @martin-guth

[GreyOrGray]

I'll play the edge case in this one - I've updated my copy of the Ola script so that when I deploy it the objects aren't created in Master. Validating the entries in the CommandLog table would still require some manual intervention or a broader search for the table.

[SQLDBAWithABeard]

We can do that already by setting this config policy.ola.database @IsItGreyOrGray

So we could add a prefix and/or suffix config for job names????

[GreyOrGray]

Damn. Missed that. Thank you!

[wsmelton]

The issue with use of the command log is it is not enabled by default on any of the jobs.

If you just take the maintenance script as is, last time I did it none of the jobs have @LogToTable set to Y. And default of that parameter in the procs is N.

[koglerk]

Pulling the job names from the CommandLog table poses another risk as well. What if I renamed a job at some point? The history of the job with the old name could still be present. You'd almost have to look at the last modified date of the agent job and only pull its CommandLog entries forward of that date.

Rather than do that, since the default names of Ola's jobs are known, couldn't we just create config entries for each and allow individuals to override the default values? The current config for Ola jobs looks like this:

If we follow that naming standard, we'd end up adding these entries to the dbachecks config:

policy.ola.deletebackuphistoryjobname = "sp_delete_backuphistory"
policy.ola.outputfilecleanupjobname = "Output File Cleanup"
policy.ola.purgejobhistoryjobname = "sp_purge_jobhistory"
policy.ola.systemfulljobname = "DatabaseBackup - SYSTEM_DATABASES - FULL"
policy.ola.systemintegritycheckjobname = "DatabaseIntegrityCheck - SYSTEM_DATABASES"
policy.ola.userdiffjobname = "DatabaseBackup - USER_DATABASES - DIFF"
policy.ola.userfulljobname = "DatabaseBackup - USER_DATABASES - FULL"
policy.ola.userindexoptimizejobname = "IndexOptimize - USER_DATABASES"
policy.ola.userintegritycheckjobname = "DatabaseIntegrityCheck - USER_DATABASES"

The syntax isn't right (obviously we'd use Set-DbcConfig) but it should get the point across. This method would mean anyone who installed Ola's jobs with their default names wouldn't have to make any changes, but those who had changed the names would be able to adjust dbachecks accordingly.

[SQLDBAWithABeard]

So this is turning into a tricky thing to resolve!

How about if I add a

policy.ola.defaultnaming

config item which is set to true for most users and then a switch when creating the jobnames array to add all names from

(Get-DbaAgentJob -SqlInstance $PSitem).Where{$_.Name -like '*$JobSearch*'}

where $JobSearch is another config item of

policy.ola.jobnamesearch

Would that resolve the issue?