DBA

How to use configure Ola Hallengren SQL server Maintenance script

December 10, 2018
Advertisements

Ola Hallengren Maintenance Plan Deployment

Many of DBAs already started using the Ola hallengren’s maintenance plan script. It is a free script replacing traditional GUI maintenance plan. It is very good script for all type and variety of database, application and size.

He is a Microsoft award winner for his maintenance plan script. Here is the steps, how to use effectively for many servers. You can use a CMS if you registered it or one be one.

Here is what, I have customized it, you need to change the DB name and backup path in all the script, copy it notepad use Ctrl+H and replace it.

  1. Run the 1_MaintenanceSolution – Change the USE DBA_DB
  2. Run the 2_Job_creation– Change the @Directory = ‘D:\SQL_Backup’
  3. Run the 3_Schedule_creation – No changes
  4. Run the 4_Update_schedule – No changes
  5. Run the 5_View_Schedule – No changes
  6. Clean-up, if needed

 

Job and SP creation

1_MaintenanceSolution:

https://ola.hallengren.com/downloads.html — You can download all in one-shot or needed one.

  1. Create a dedicated DB called DBA_DB or by default it uses master DB and run the downloaded script

USE [master] — Specify the database in which the objects will be created.

There are lots of parameters in each job. Please open the website and read it. I have included the important parameters.

Parameters Ex: Skip database “USER_DATABASES, -Db1″mostly I used.

Value Description
SYSTEM_DATABASES All system databases (master, msdb, and model)
USER_DATABASES All user databases
ALL_DATABASES All databases
AVAILABILITY_GROUP_DATABASES All databases in availability groups
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES All user databases that are not in availability groups
Db1 The database Db1
Db1, Db2 The databases Db1 and Db2
USER_DATABASES, -Db1 All user databases, except Db1
%Db% All databases that have “Db” in the name
%Db%, -Db1 All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db% All databases that do not have “Db” in the name

 

Changes needed In the script:

  1. Change the use master to DBA_DB,If we do not have DBA_DB, create one Or you can go with master.
  2. Change the backup location “@BackupDirectory” in the SET parameter, per organization location (You can take a look of the backup history system table for the location)

The Ola script take a root directory from location “@BackupDirectory”  and create a sub directory per DBs

Ex: Root: C:\backup

Per DB: full backup : C:\backup\servername\DB_name\full\ & Diff backup : C:\backup\servername\DB_name\diff\ & Log backup : C:\backup\servername\DB_name\log\

Run the script. It will create a four SP, one table in the DBA_DB and it will create 11 jobs in the agent, two will be removed by customized script “[sp_delete_backuphistory] & [sp_purge_jobhistory]”, I need this for history use.

Update retention by day or hours on the steps

Note:  Change the Verify = ‘N’ for VLDB larger databases.

Example:

 

EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @Directory = ‘D:\SQL_Backup’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 192, @CheckSum = ‘Y’, @ChangeBackupType=’Y’, @LogToTable = ‘Y’

This is an important step. The job by default will create with retention NULL, we need to open each step and need to pass the retention days or hours as below.

2_Job_creation:

Use the notepad and replace script of backup location – Make sure you have changed the backup location in the notepad.

First use CTRL+H and replace the backup location with right path Ex: D:\SQL_Backup

Previously he used CMD mode of scripts.

==========================================

-- change the backup path D:\SQL_Backup


USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'CommandLog Cleanup', @step_id=1, 
    @command=N'-- Job: Command Log Cleanup


DELETE FROM [dbo].[CommandLog]
WHERE StartTime < DATEADD(dd,-130,GETDATE())
'
GO

GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', @step_id=1,
    @command=N'-- Job: DatabaseBackup - SYSTEM_DATABASES - FULL

EXECUTE [dbo].[DatabaseBackup]
@Databases = ''SYSTEM_DATABASES'',
@Directory = ''D:\SQL_Backup'',
@BackupType = ''FULL'',
@Verify = ''Y'',
@CleanupTime = 192,
@CheckSum = ''Y'',
@LogToTable = ''Y''
'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', @step_id=1,
    @command=N'-- Job: DatabaseBackup - USER_DATABASES - DIFF

EXECUTE [dbo].[DatabaseBackup]
@Databases = ''USER_DATABASES'',
@Directory = ''D:\SQL_Backup'',
@BackupType = ''DIFF'',
@Verify = ''Y'',
@CleanupTime = 192,
@CheckSum = ''Y'',
@ChangeBackupType=''Y'',
@LogToTable = ''Y''
'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @step_id=1,
    @command=N'-- Job: DatabaseBackup - USER_DATABASES - FULL

EXECUTE [dbo].[DatabaseBackup]
@Databases = ''USER_DATABASES'',
@Directory = ''D:\SQL_Backup'',
@BackupType = ''FULL'',
@Verify = ''N'',
@CleanupTime = 336,
@CheckSum = ''Y'',
@ChangeBackupType=''Y'',
@LogToTable = ''Y''
'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'DatabaseBackup - USER_DATABASES - LOG', @step_id=1,
    @command=N'-- Job: DatabaseBackup - USER_DATABASES - LOG

EXECUTE [dbo].[DatabaseBackup]
@Databases = ''USER_DATABASES'',
@Directory = ''D:\SQL_Backup'',
@BackupType = ''LOG'',
@Verify = ''Y'',
@CleanupTime = 192,
@CheckSum = ''Y'',
@ChangeBackupType=''Y'',
@LogToTable = ''Y''
'
GO



USE [msdb]
GO
EXEC msdb.dbo.sp_update_jobstep @job_name=N'IndexOptimize - USER_DATABASES', @step_id=1,
    @command=N'-- Job: IndexOptimize - USER_DATABASES

EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'', @UpdateStatistics = ''ALL'',@OnlyModifiedStatistics = ''Y'',@PartitionLevel = ''Y'',@LogToTable = ''Y''
'
GO

 

==========================================

Calculation to Days: 336/24 = 14 Days. The retention can be changed.

Job Name Default Value We need to change Example
Command Log Cleanup 30 Days 130 Days (dd,-130,GETDATE())
DatabaseBackup – SYSTEM_DATABASES – FULL NULL 120 Hours @CleanupTime =120
DatabaseBackup – USER_DATABASES – DIFF NULL 192 Hours @CleanupTime =192
DatabaseBackup – USER_DATABASES – FULL NULL 336 Hours @CleanupTime =336
DatabaseBackup – USER_DATABASES – LOG NULL 192 Hours @CleanupTime =192
DatabaseIntegrityCheck – SYSTEM_DATABASES N/A N/A N/A
DatabaseIntegrityCheck – USER_DATABASES N/A N/A N/A
IndexOptimize – USER_DATABASES N/A N/A N/A
Output File Cleanup 30 Days No Changes N/A

 

Create and Update job schedule:

This master script by default will not create a schedule.

I have customized script for that, which will create a following DBA standard schedule. The schedule can be changed for larger databases and more than one instance running on the server.

3_Schedule_creation

/*
Jobname passed in @Jname_OLA_CommandLog_Cleanup.

*/

/*CommandLog Cleanup schedule Weekly wed @12:10 PM*/
Use MSDB
GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_CommandLog_Cleanup varchar(100)
DECLARE @S_OLA_CommandLog_Cleanup varchar(100)
set @Jname_OLA_CommandLog_Cleanup ='CommandLog Cleanup'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_CommandLog_Cleanup

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_CommandLog_Cleanup)
Begin
set @S_OLA_CommandLog_Cleanup='OLA_'+ @Jname_OLA_CommandLog_Cleanup
Print 'Schedule already available, Please check '+@Jname_OLA_CommandLog_Cleanup
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_CommandLog_Cleanup --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_CommandLog_Cleanup='OLA_'+ @Jname_OLA_CommandLog_Cleanup
EXEC  msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_CommandLog_Cleanup, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=8, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=121000, 
    @active_end_time=235959
end

/* DatabaseBackup - SYSTEM_DATABASES - FULL schedule Daily  @12:00 PM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL varchar(100)
DECLARE @S_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL varchar(100)
set @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL ='DatabaseBackup - SYSTEM_DATABASES - FULL'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL)
Begin
set @S_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL='OLA_'+ @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL='OLA_'+ @Jname_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL
EXEC  msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseBackup_SYSTEM_DATABASES_FULL, 
    @enabled=1, 
    @freq_type=4, 
    @freq_interval=1, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=0, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=120000, 
    @active_end_time=235959
end

/* DatabaseBackup - DatabaseBackup - USER_DATABASES - DIFF schedule Daily  @1:45 AM, other than full bak*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF varchar(100)
DECLARE @S_OLA_DatabaseBackup_USER_DATABASES_DIFF varchar(100)
set @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF ='DatabaseBackup - USER_DATABASES - DIFF'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF)
Begin
set @S_OLA_DatabaseBackup_USER_DATABASES_DIFF='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseBackup_USER_DATABASES_DIFF --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseBackup_USER_DATABASES_DIFF='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_DIFF
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseBackup_USER_DATABASES_DIFF, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=126, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=14500, 
    @active_end_time=235959
end

/* DatabaseBackup - DatabaseBackup - USER_DATABASES - FULL schedule Sunday  @3:00 AM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL varchar(100)
DECLARE @S_OLA_DatabaseBackup_USER_DATABASES_FULL varchar(100)
set @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL ='DatabaseBackup - USER_DATABASES - FULL'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL)
Begin
set @S_OLA_DatabaseBackup_USER_DATABASES_FULL='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseBackup_USER_DATABASES_FULL
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseBackup_USER_DATABASES_FULL --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseBackup_USER_DATABASES_FULL='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_FULL
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseBackup_USER_DATABASES_FULL, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=1, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=30000, 
    @active_end_time=235959
end

/* DatabaseBackup - DatabaseBackup - USER_DATABASES - LOG schedule 1 hour once  @12:15 AM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG varchar(100)
DECLARE @S_OLA_DatabaseBackup_USER_DATABASES_LOG varchar(100)
set @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG ='DatabaseBackup - USER_DATABASES - LOG'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG)
Begin
set @S_OLA_DatabaseBackup_USER_DATABASES_LOG='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseBackup_USER_DATABASES_LOG
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseBackup_USER_DATABASES_LOG --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseBackup_USER_DATABASES_LOG='OLA_'+ @Jname_OLA_DatabaseBackup_USER_DATABASES_LOG
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseBackup_USER_DATABASES_LOG, 
    @enabled=1, 
    @freq_type=4, 
    @freq_interval=1, 
    @freq_subday_type=8, 
    @freq_subday_interval=1, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=0, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=1400, 
    @active_end_time=235959
end

/* DatabaseBackup - DatabaseIntegrityCheck - SYSTEM_DATABASES schedule Firday @1:30 AM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES varchar(100)
DECLARE @S_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES varchar(100)
set @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES ='DatabaseIntegrityCheck - SYSTEM_DATABASES'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES)
Begin
set @S_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES='OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES='OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseIntegrityCheck_SYSTEM_DATABASES, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=32, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=13000, 
    @active_end_time=235959
end

/* DatabaseBackup - DatabaseIntegrityCheck - USER_DATABASES schedule Firday @3:00 AM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES varchar(100)
DECLARE @S_OLA_DatabaseIntegrityCheck_USER_DATABASES varchar(100)
set @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES ='DatabaseIntegrityCheck - USER_DATABASES'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES)
Begin
set @S_OLA_DatabaseIntegrityCheck_USER_DATABASES='OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES
Print 'Schedule already available, Please check '+@Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_DatabaseIntegrityCheck_USER_DATABASES --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_DatabaseIntegrityCheck_USER_DATABASES='OLA_'+ @Jname_OLA_DatabaseIntegrityCheck_USER_DATABASES
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_DatabaseIntegrityCheck_USER_DATABASES, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=32, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=30000, 
    @active_end_time=235959
end

/* DatabaseBackup - IndexOptimize - USER_DATABASES schedule Saturday @3:00 AM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_IndexOptimize_USER_DATABASES varchar(100)
DECLARE @S_OLA_IndexOptimize_USER_DATABASES varchar(100)
set @Jname_OLA_IndexOptimize_USER_DATABASES ='IndexOptimize - USER_DATABASES'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_IndexOptimize_USER_DATABASES

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_IndexOptimize_USER_DATABASES)
Begin
set @S_OLA_IndexOptimize_USER_DATABASES='OLA_'+ @Jname_OLA_IndexOptimize_USER_DATABASES
Print 'Schedule already available, Please check '+@Jname_OLA_IndexOptimize_USER_DATABASES
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_IndexOptimize_USER_DATABASES --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_IndexOptimize_USER_DATABASES='OLA_'+ @Jname_OLA_IndexOptimize_USER_DATABASES
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_IndexOptimize_USER_DATABASES, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=64, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=30000, 
    @active_end_time=235959
end

/* DatabaseBackup - Output File Cleanup schedule Wednesday @1:10 PM*/

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_Output_File_Cleanup varchar(100)
DECLARE @S_OLA_Output_File_Cleanup varchar(100)
set @Jname_OLA_Output_File_Cleanup ='Output File Cleanup'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_Output_File_Cleanup

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_Output_File_Cleanup)
Begin
set @S_OLA_Output_File_Cleanup='OLA_'+ @Jname_OLA_Output_File_Cleanup
Print 'Schedule already available, Please check '+@Jname_OLA_Output_File_Cleanup
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_Output_File_Cleanup --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_Output_File_Cleanup='OLA_'+ @Jname_OLA_Output_File_Cleanup
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_Output_File_Cleanup, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=8, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170414, 
    @active_end_date=99991231, 
    @active_start_time=131000, 
    @active_end_time=235959
end

/* We do not need this two job */

USE [msdb]
GO

EXEC msdb.dbo.sp_delete_job @job_name=N'sp_delete_backuphistory', @delete_unused_schedule=1
GO

EXEC msdb.dbo.sp_delete_job @job_name=N'sp_purge_jobhistory', @delete_unused_schedule=1
GO




/*

-- DatabaseBackup - sp_delete_backuphistory schedule Wednesday @2:10 PM

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_sp_delete_backuphistory varchar(100)
DECLARE @S_OLA_sp_delete_backuphistory varchar(100)
set @Jname_OLA_sp_delete_backuphistory ='sp_delete_backuphistory'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_sp_delete_backuphistory

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_sp_delete_backuphistory)
Begin
set @S_OLA_sp_delete_backuphistory='OLA_'+ @Jname_OLA_sp_delete_backuphistory
Print 'Schedule already available, Please check '+@Jname_OLA_sp_delete_backuphistory
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_sp_delete_backuphistory --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_sp_delete_backuphistory='OLA_'+ @Jname_OLA_sp_delete_backuphistory
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_sp_delete_backuphistory, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=8, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=141000, 
    @active_end_time=235959
end

-- DatabaseBackup - sp_purge_jobhistory schedule Wednesday @3:10 PM

GO
DECLARE @jobId BINARY(16)
DECLARE @Jname_OLA_sp_purge_jobhistory varchar(100)
DECLARE @S_OLA_sp_purge_jobhistory varchar(100)
set @Jname_OLA_sp_purge_jobhistory ='sp_purge_jobhistory'

-- select * from msdb.dbo.sysjobs where name = @jobname
select @jobId=job_id from msdb.dbo.sysjobs where name = @Jname_OLA_sp_purge_jobhistory

-- select * FROM [msdb].[dbo].[sysschedules]
IF EXISTS (select name FROM [msdb].[dbo].[sysschedules] where name = 'OLA_'+ @Jname_OLA_sp_purge_jobhistory)
Begin
set @S_OLA_sp_purge_jobhistory='OLA_'+ @Jname_OLA_sp_purge_jobhistory
Print 'Schedule already available, Please check '+@Jname_OLA_sp_purge_jobhistory
--EXEC msdb.dbo.sp_delete_schedule  @schedule_name = @S_OLA_sp_purge_jobhistory --,@force_delete = 1;  
END

ELSE
BEGIN
set @S_OLA_sp_purge_jobhistory='OLA_'+ @Jname_OLA_sp_purge_jobhistory
EXEC  msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@S_OLA_sp_purge_jobhistory, 
    @enabled=1, 
    @freq_type=8, 
    @freq_interval=8, 
    @freq_subday_type=1, 
    @freq_subday_interval=0, 
    @freq_relative_interval=0, 
    @freq_recurrence_factor=1, 
    @active_start_date=20170413, 
    @active_end_date=99991231, 
    @active_start_time=151000, 
    @active_end_time=235959
end

*/

DBA standard Ola maintenance schedule:

We can change the schedule, whichever applicable to your environment.

Job Name Run day Time Retention Days Retention Hours
CommandLog Cleanup Wednesday 12:10AM  130 Days  N/A
DatabaseBackup – SYSTEM_DATABASES – FULL Daily 12 PM  5 Days 120 Hours
DatabaseBackup – USER_DATABASES – DIFF Daily- Other than sunday 1:45AM  8 Days 192 Hours
DatabaseBackup – USER_DATABASES – FULL Sunday 3AM 14 Days 336 Hours
Database Backup – USER_DATABASES – LOG Hourly 12:15AM 8 Days 192 Hours
DatabaseIntegrityCheck – SYSTEM_DATABASES Friday 1:30AM N/A N/A
DatabaseIntegrityCheck – USER_DATABASES Friday 3AM N/A N/A
IndexOptimize – USER_DATABASES Saturday 3AM N/A N/A
Output File Cleanup Wednesday 1:10AM 30 Days N/A

 

Schedule GUI maintenance plan cleanup

Check the older backup files in path for any other DBA jobs/maintenance plan and create one common cleanup by the maintenance plan for the root folder,including all sub folders, with the schedule 2 weeks old can be deleted and put the end date one month for a job in the agent job, run it on a daily.

Update Contact information on job

This script will update the contact information in the job.

4_Update_schedule

 

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'CommandLog Cleanup', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
Clean up the logging table data - Retention 130 Days'

GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
SystemDB full backup daily: Clean up old backup - Retention 5 Days (24*5= 120 Hours)'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
UserDB Differential backup daily: Clean up old backup - Retention 8 Days (24*8= 192 Hours)'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
UserDB full backup weekly: Clean up old backup - Retention 14 Days (24*14= 336 Hours)'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
UserDB Log backup daily: Clean up old backup - Retention 8 Days (24*8= 192 Hours)'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
SystemDB Database Integrity Check'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
UserDB Database Integrity Check'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'IndexOptimize - USER_DATABASES', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
UserDB Database Index Optimization'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Output File Cleanup', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
Output File Cleanup for the jobs - Retention 30 Days'
GO
/*
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sp_delete_backuphistory', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
SP delete backup history - Retention 600 Days'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'sp_purge_jobhistory', 
    @description=N'Source: https://ola.hallengren.com - Used by: DBA Team
Sp purge job history - Retention 600 Days'
GO
*/

View schedule

Run the Schedule job script and make sure, the schedule matches with your creation.

5_View_Schedule

Run this attached script.

SELECT 
    --[schedule_uid] AS [ScheduleID],
    [name] AS [ScheduleName]
    , CASE [enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]
    , STUFF(
            STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageStartDate]
    , STUFF(
            STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
                , 8, 0, '-') AS [ScheduleUsageEndDate]
    , [date_created] AS [ScheduleCreatedOn]
    , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
where [name] like 'ola%'
and [enabled] = 1 -- 1 enabled
ORDER BY Recurrence

How to read and verify the backup and other maintenance success, failure and command ect.

The following table is the centralized table for the maintenance.

select*from DBA_DB.[dbo].[CommandLog]

 

For a more detailed view, open the SQL error log location – sp_readerrorlog 0,1,’startup’

There will be a separate file per run and the file retention is 30 days. We sort date modified can check, which order the command executed, parameter files and the status of the job.

Ex: C:\Program Files\Microsoft SQL Server\MSSQL11.AIR17\MSSQL\Log — errorlog location

File name: DatabaseBackup_0x7C0253C979CA264BA3B751FF64AABE1E_1_20170514_220000

Cleanup: Delete all the 11 jobs and SPs

This script will delete all the jobs and SPs not the table CommandLog . – This is for complete cleanup.

USE [msdb]
GO
/****** Object:  Job [CommandLog Cleanup]    Script Date: 04/19/2017 03:04:50 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'CommandLog Cleanup')
EXEC msdb.dbo.sp_delete_job @job_name=N'CommandLog Cleanup', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [DatabaseBackup - SYSTEM_DATABASES - FULL]    Script Date: 04/19/2017 03:05:54 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseBackup - SYSTEM_DATABASES - FULL')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [DatabaseBackup - USER_DATABASES - DIFF]    Script Date: 04/19/2017 03:06:22 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseBackup - USER_DATABASES - DIFF')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [DatabaseBackup - USER_DATABASES - FULL]    Script Date: 04/19/2017 03:06:37 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseBackup - USER_DATABASES - FULL')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @delete_unused_schedule=1

else 
print 'No job created'


USE [msdb]
GO
/****** Object:  Job [DatabaseBackup - USER_DATABASES - LOG]    Script Date: 04/19/2017 03:07:01 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseBackup - USER_DATABASES - LOG')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [DatabaseIntegrityCheck - SYSTEM_DATABASES]    Script Date: 04/19/2017 03:07:28 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseIntegrityCheck - SYSTEM_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', @delete_unused_schedule=1


else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [DatabaseIntegrityCheck - USER_DATABASES]    Script Date: 04/19/2017 03:08:06 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DatabaseIntegrityCheck - USER_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [IndexOptimize - USER_DATABASES]    Script Date: 04/19/2017 03:08:24 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'IndexOptimize - USER_DATABASES')
EXEC msdb.dbo.sp_delete_job @job_name=N'IndexOptimize - USER_DATABASES', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [Output File Cleanup]    Script Date: 04/19/2017 03:08:43 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Output File Cleanup')
EXEC msdb.dbo.sp_delete_job @job_name=N'Output File Cleanup', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [sp_delete_backuphistory]    Script Date: 04/19/2017 03:09:00 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sp_delete_backuphistory')
EXEC msdb.dbo.sp_delete_job @job_name=N'sp_delete_backuphistory', @delete_unused_schedule=1

else 
print 'No job created'

USE [msdb]
GO
/****** Object:  Job [sp_purge_jobhistory]    Script Date: 04/19/2017 03:09:17 ******/
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'sp_purge_jobhistory')
EXEC msdb.dbo.sp_delete_job @job_name=N'sp_purge_jobhistory', @delete_unused_schedule=1


else 
print 'No job created'


--========================== SP drop ==========================

USE [DBA_DB]


DROP PROCEDURE [dbo].[CommandExecute]
print ' [DatabaseBackup] SP droped or not exist'

USE [DBA_DB]

DROP PROCEDURE [dbo].[DatabaseBackup]

print '[DatabaseBackup] SP droped or not exist'



USE [DBA_DB]

DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]

print '[DatabaseIntegrityCheck] SP droped or not exist'


USE [DBA_DB]

DROP PROCEDURE [dbo].[IndexOptimize]

print '[DatabaseIntegrityCheck] SP droped or not exist'

For AlwaysON database

How should I configure DatabaseBackup to back up an Availability Group?

https://ola.hallengren.com/frequently-asked-questions.html

once you specify the backup preference in the alwaysON group, the script will automatically take the preferred replica.

Primary – Like a normal stand-lone server – Full, differential and Transaction log.

Secondary – Copy only full, no differential allowed and normal Transaction log. – add a parameter @CopyOnly=’Y’

I have had one of legacy server and which does not have a compression feature and we used a third party software to compress and we have not have much control to change the  backup extension .SQLBAK  to take a benefit of it and I emailed him to add a parameter to pass as a backup extension and he helped it.

Again, big thanks to Ola Hallengren and maintaining with update SQL version releases.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

78 + = 80