Elastic jobs, part two

In one of my previous blogs, I wrote about how to create an elastic job agent when you need the SQL Agent functionality on Azure. You can read that one here.

This morning, I needed a job to update the stats on a database, but on just one database within the “instance” on Azure. But my first group contained all the databases, and the Ola Hallengren script isn’t available on all databases and the credential I’m using to execute the jobs doesn’t have access to all the databases.

So, I needed a new group. It turns out, it’s not that hard to create a new group that contains one, or more, databases.

First, create a new group:

-- create new target group for just one database
 EXEC jobs.sp_add_target_group 'GroupMdw'

This will finish instantly as long as the name is unique. Second, create members of the group:

EXEC jobs.sp_add_target_group_member
 @target_group_name='GroupMdw',
 @target_type='SqlDatabase',
 @server_name='sql-server-prod.database.windows.net',
 @database_name='sqldb-mdw-prod'

Now I’ve got my group with just the one database. Let’s create the statistics job:

EXEC jobs.sp_add_job 
     @job_name ='StatisticsMaintenance', 
     @description='Update the stats',
     @schedule_interval_type = 'Hours',
     @schedule_interval_count = 6,
     @enabled = 1,
     @schedule_start_time = '2020-11-12 06:00:00'

This job will run every 6 hours. You could schedule it once a day, once a week or every minute.

Now for the steps the job needs to take:

EXEC jobs.sp_add_jobstep
 @job_name='StatisticsMaintenance',
 @step_id = 1,
 @command=N'EXECUTE dbo.IndexOptimize
 @Databases = ''USER_DATABASES'',
 @FragmentationLow = NULL,
 @FragmentationMedium = NULL,
 @FragmentationHigh = NULL,
 @UpdateStatistics = ''ALL'',
 @StatisticsModificationLevel = 1',
 @credential_name='elasticJobCred',
 @target_group_name='GroupMdw'

The target group name is essential in this job step. This is the variable that will point the job step into the correct database or databases!

One last word of warning. The job execution starts immedeatly after the creation of the job IF the schedule_start_time is in the past.

Thanks for reading!