Elastic Agent Jobs

elastic_agent_jobs.sql

-- Elastic Agent Jobs
-- https://sqlkitty.com/elastic-jobs-azure-sql-db/

-- Add target group
EXEC jobs.sp_add_target_group 'AzureSQLDBs';

-- Add single database (or server/elastic pool) to target group
EXEC jobs.sp_add_target_group_member
@target_group_name = 'AzureSQLDBs',
@target_type = 'SqlDatabase',
@server_name = '<SERVER_NAME>.database.windows.net',
@database_name = '<DB_NAME>';

-- Add job
EXEC jobs.sp_add_job 
@job_name = 'IndexMaintenance', 
@description = 'Run stored procedure dbo.IndexOptimize'; 

-- Add job step
EXEC jobs.sp_add_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''N'';',
@target_group_name = 'AzureSQLDBs';

-- Update job step
EXEC jobs.sp_update_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''Y'';',
@target_group_name = 'AzureSQLDBs';

-- Add 2nd job step
EXEC jobs.sp_add_jobstep 
@job_name = 'IndexMaintenance',
@step_name = 'Clean CommandLog',
@command = N'DELETE FROM [dbo].[CommandLog]
WHERE StartTime <= DATEADD(DAY, -30, GETDATE());',
@target_group_name = 'AzureSQLDBs';

-- Start job
EXEC jobs.sp_start_job 'IndexMaintenance';

-- Set job schedule
EXEC jobs.sp_update_job
@job_name = 'IndexMaintenance',
@enabled=1,
@schedule_interval_type = 'Days',
@schedule_interval_count = 1, 
@schedule_start_time = '2024-01-01 00:00:00'; 

-- Generate stop job commands
SELECT 
'EXEC jobs.sp_stop_job '''+ CAST(job_execution_id AS CHAR(36)) +''';' AS [command],
*
FROM jobs.job_executions
WHERE job_name = 'IndexMaintenance' and is_active = 1
ORDER BY start_time DESC;

-- Target group details
SELECT * FROM jobs.target_group_members;

-- Job details
SELECT * FROM jobs.jobs;

-- Job step details
SELECT js.*
FROM jobs.jobsteps js
JOIN jobs.jobs j
ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- Job run history
 SELECT 
job_name,
step_name,
target_server_name,
target_database_name,
target_type, 
last_message,
start_time,
end_time,
is_active,
lifecycle,
current_attempts
FROM jobs.job_executions 
ORDER BY start_time DESC;

elastic_agent_provision.tf

/*
--Assign as identity to elastic job agent
--master db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
--user db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER ElasticJobsIdentity;
*/
resource "azurerm_user_assigned_identity" "elastic_jobs" {
  location            = azurerm_resource_group.default.location
  name                = "ElasticJobsIdentity"
  resource_group_name = azurerm_resource_group.default.name
}
resource "azurerm_mssql_job_agent" "elastic_jobs" {
  name        = "ElasticJobsAgent"
  location    = azurerm_resource_group.default.location
  database_id = azurerm_mssql_database.elastic_jobs.id
}
resource "azurerm_mssql_database" "elastic_jobs" {
  name      = "ElasticJobsDB"
  server_id = azurerm_mssql_server.default.id
}