Introduction
If you have you ever struggled with setting up a maintenance plan for SQL Server, you’ll be glad that Ola Hallengren came around. This very clever SQL Server DBA thought, well the SQL Server Maintenance Plans aren’t that great. It does the job, but out of the box some jobs are scheduled at exactly the same time, amongst other inefficiencies. We all can be thankful that he wrote his own MaintenanceSolution.sql, which has won several awards and can be downloaded for free here.
F5 and then?
If you have downloaded Ola Hallengren’s MaintenanceSolution.sql, you still have to schedule the SQL Agent Jobs. But it is logical that this is not included, as every company, every server could have different requirements for backup, integrity checks, index optimization and statistics maintenance.
.. Configure!
In the script below you will find a CONFIGURATION section where you can configure how the SQL Agent Jobs created by Ola Hallengren should be scheduled. Just for your convenience, hope you like it.
--\
---) Author: Hans Michiels
---) Creates jobschedules in SQL Server Agent jobs that
---) were created by Ola Hallengren's maintenance solution.
--/
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
USE [msdb]
GO
--\-------------------------------------------------------------------------------------
---) DECLARATIONS
--/-------------------------------------------------------------------------------------
DECLARE @cur_days VARCHAR(50)
-- @cur_days contains a space separated list of weekdays, e.g. 'mon tue wed thu fri sat sun',
-- or use 'all' for all days.
-- Please note that using 'mon tue wed thu fri sat sun' will create a weekly job with
-- all days checked while using 'all' will create a daily job.
DECLARE @cur_start_time INT
-- @cur_start_time: the starttime of the job in integer format (hhmmss)
DECLARE @cur_subday_interval INT
-- @cur_subday_interval: N in 'every N seconds/minutes/hours (for seconds max N = 100)
-- Use 0 when N/A
DECLARE @cur_intraday_unit VARCHAR(7)
-- @cur_intraday_unit: seconds (s), minutes (m) or hours (h).
-- For configuration you can use both the full name or the first letter.
DECLARE @cur_delete_any_schedule CHAR(1)
-- @cur_delete_any_schedule: indicates if any schedule for the given jobname should be deleted.
-- Use NULL if you want to use the default value (recommended).
-- It is recommended only to use 'N' if you use multiple schedules for the same job.
DECLARE @default_delete_any_schedule CHAR(1)
-- @default_delete_any_schedule: valued used if in the configuration below
-- [delete_any_schedule] is NULL for a specific job.
DECLARE @delete_schedules_only CHAR(1)
-- @delete_schedules_only: if set to 'Y', only the existing schedules are deleted.
-- No new schedules are made.
-- If you use this setting, make sure to set @default_delete_any_schedule also to 'Y'
-- without overruling it to 'N' for specific jobs.
DECLARE @cur_schedule_id int
DECLARE @cur_job_name SYSNAME
DECLARE @cur_schedule_name SYSNAME
DECLARE @cur_freq_type INT
DECLARE @cur_freq_interval INT
DECLARE @cur_freq_subday_type INT
DECLARE @active_start_date INT
DECLARE @message VARCHAR(500)
SET @active_start_date = CONVERT(INT, CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112));
--\-------------------------------------------------------------------------------------
---) CONFIGURATION
--/-------------------------------------------------------------------------------------
SET @default_delete_any_schedule = 'Y';
-- If you DO NOT want to delete all existing schedules for all jobs in your configuration,
-- change @default_delete_any_schedule to 'N'.
-- Keeping this value on 'Y' is usually safe to do and this prevents that old schedules
-- might in some cases not be deleted which can give trouble due to multiple (conflicting)
-- schedules.
-- !! ONLY IF you have more than one schedule for a job, you should set [delete_any_schedule]
-- in the configuration above to 'N' for that specific job.
SET @delete_schedules_only = 'N';
DECLARE daily_weekly_cursor CURSOR LOCAL STATIC FOR
SELECT CONVERT(SYSNAME, NULL) AS [job_name], CONVERT(SYSNAME, NULL) AS [schedule_name],
CONVERT(INT, NULL) AS [start_time], CONVERT(VARCHAR(50), NULL) AS [days],
CONVERT(INT, NULL) AS [subday_interval],
CONVERT(VARCHAR(7), NULL) AS [intraday_unit],
CONVERT(CHAR(1), NULL) AS [delete_any_schedule]
-------------[job_name]------------------------------------[schedule]--[start]-[days]-----------------------[subday ]-[intraday]-[delete_any]
-----------------------------------------------------------[name ]--[time ]------------------------------[interval]-[unit ]-[schedule ]
UNION SELECT 'CommandLog Cleanup' , 'Schedule', 180000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'Output File Cleanup' , 'Schedule', 180500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_delete_backuphistory' , 'Schedule', 181000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_purge_jobhistory' , 'Schedule', 181500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES' , 'Schedule', 182000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseIntegrityCheck - USER_DATABASES' , 'Schedule', 191500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL' , 'Schedule', 001500, 'all' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 003000, 'all' , 0 , NULL , NULL
UNION SELECT 'IndexOptimize - USER_DATABASES' , 'Schedule', 081000, 'mon tue wed thu fri sat sun', 0 , NULL , NULL
/* -- Weekly full, other days diff example:
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 201500, 'fri' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - DIFF' , 'Schedule', 201500, 'mon tue wed thu sat sun' , 0 , NULL , NULL
*/
/* -- Transaction log example / Example of use of [delete_any_schedule] with more than one schedule
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekdays', 000001, 'mon tue wed thu fri' , 10 , 'minutes', 'Y'
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekend' , 000001, 'sat sun' , 2 , 'hours', 'N'
*/
--\-------------------------------------------------------------------------------------
---) CREATION OF THE SCHEDULES
--/-------------------------------------------------------------------------------------
OPEN daily_weekly_cursor
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
@cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
WHILE @@FETCH_STATUS = 0
BEGIN
--\
---) Check if it is the dummy row to specify datatypes:
--/
IF @cur_job_name IS NULL
BEGIN
-- RAISERROR('NULL', 0, 1) WITH NOWAIT;
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
@cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
END
SET @message = 'Job: ' + @cur_job_name
RAISERROR(@message , 0, 1) WITH NOWAIT;
--\
---) Get the current schedule_id, if any.
--/
SELECT @cur_schedule_id = -1;
WHILE @cur_schedule_id IS NOT NULL
BEGIN
SELECT @cur_schedule_id = (
SELECT TOP 1 sch.schedule_id
FROM msdb.dbo.sysschedules sch
JOIN msdb.dbo.sysjobschedules js
ON js.schedule_id = sch.schedule_id
JOIN msdb.dbo.sysjobs jobs
ON jobs.job_id = js.job_id
WHERE jobs.name = @cur_job_name
AND ( ISNULL(@cur_delete_any_schedule, @default_delete_any_schedule) = 'Y' OR sch.name = @cur_schedule_name )
);
--\
---) If schedule exists, delete it first.
--/
IF @cur_schedule_id IS NOT NULL
BEGIN
EXEC msdb.dbo.sp_delete_schedule @schedule_id = @cur_schedule_id, @force_delete = 1;
SET @message = ' ' + 'Schedule deleted with @schedule_id = ' + CONVERT(VARCHAR, @cur_schedule_id)
RAISERROR(@message , 0, 1) WITH NOWAIT;
END
END
--\
---) Set @cur_freq_type depending on days specification.
--/
SELECT @cur_freq_type = CASE @cur_days WHEN 'all' THEN 4 ELSE 8 END;
--\
---) Set @cur_freq_interval depending on days specification.
--/
SELECT @cur_freq_interval = 0, @cur_days = ' ' + @cur_days + ' ';
IF CHARINDEX(' all ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
IF CHARINDEX(' sun ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
IF CHARINDEX(' mon ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 2;
IF CHARINDEX(' tue ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 4;
IF CHARINDEX(' wed ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 8;
IF CHARINDEX(' thu ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 16;
IF CHARINDEX(' fri ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 32;
IF CHARINDEX(' sat ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 64;
IF @cur_freq_interval = 0
BEGIN
RAISERROR('Days string does not contain any valid day abbreviations', 16, 1);
END
IF @delete_schedules_only != 'Y'
BEGIN
IF @cur_subday_interval = 0
BEGIN
EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
@enabled=1,
@freq_type=@cur_freq_type,
@freq_interval=@cur_freq_interval,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=@active_start_date,
@active_end_date=99991231,
@active_start_time=@cur_start_time,
@active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;
SET @message = ' ' + 'Schedule created for days' + @cur_days + 'at ' + CONVERT(VARCHAR, @cur_start_time)
RAISERROR(@message , 0, 1) WITH NOWAIT;
END ELSE BEGIN
SELECT @cur_freq_subday_type =
CASE LOWER(LEFT(@cur_intraday_unit, 1))
WHEN 'h' THEN 8
WHEN 'm' THEN 4
WHEN 's' THEN 2
ELSE 0
END
IF @cur_freq_subday_type = 0
BEGIN
RAISERROR('''intraday_unit'' must contain a valid value when ''subday_interval'' contains a number greater than 0. Please check your configuration.', 16, 1);
END
EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
@enabled=1,
@freq_type=@cur_freq_type,
@freq_interval=@cur_freq_interval,
@freq_subday_type=@cur_freq_subday_type,
@freq_subday_interval=@cur_subday_interval,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=@active_start_date,
@active_end_date=99991231,
@active_start_time=@cur_start_time,
@active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;
SET @message = ' ' + 'Schedule created for days' + @cur_days + 'every ' + CONVERT(VARCHAR, @cur_subday_interval) + ' ' + @cur_intraday_unit
RAISERROR(@message , 0, 1) WITH NOWAIT;
END
END
RAISERROR('-----' , 0, 1) WITH NOWAIT;
SET @cur_schedule_id = NULL;
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
@cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
END
CLOSE daily_weekly_cursor
DEALLOCATE daily_weekly_cursor
Download the script here.
(Note: the downloadable script has slightly better indenting than the html version above).
Conclusion / Wrap up
With this script you can easily schedule the SQL Agent Jobs that Ola Hallengren’s MaintenanceSolution-script creates.