Tag Archives: Maintenance Solution

Schedule Ola Hallengren’s Maintenance Solution (T-SQL Scripting Series)

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.

Ola_Scheduling.sql

--\
---) 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).

TSQL-S01E02-Schedule

Conclusion / Wrap up

With this script you can easily schedule the SQL Agent Jobs that Ola Hallengren’s MaintenanceSolution-script creates.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.