How to Set Up and Utilize Ola Hallengren's SQL Server Maintenance Scripts

In this blog, we'll explore how to set up and utilize Ola Hallengren's scripts, with a focus on the CommandLog table. By leveraging these scripts, you can ensure that your SQL Server environment remains healthy and performs optimally, all while having detailed logs of each task's execution time.

Stay tuned for a step-by-step guide on implementing these powerful scripts and making the most out of the CommandLog table to streamline your SQL Server maintenance tasks.

1ola.hallengren.com

How to Set Up and Utilize Ola Hallengren's SQL Server Maintenance Scripts

When it comes to maintaining SQL Server environments, efficiency and reliability are paramount. Ola Hallengren's SQL Server Maintenance Solution is renowned for its comprehensive and customizable set of scripts that handle backups, integrity checks, and index and statistics maintenance. Here's how you can set up and make the most out of these powerful scripts.

Step 1: Download and Install the Scripts

1. Download the Scripts:

   • Visit Ola Hallengren's website and download the maintenance solution scripts.

2. Run the Scripts:

   • Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

   • Open the downloaded script file in SSMS and execute it. This will create the necessary stored procedures, functions, and tables in the master database.

     
     

Step 2: Schedule the Maintenance Tasks

1. Open SQL Server Agent:

   • Expand the SQL Server Agent node in SSMS.

   • Right-click on “Jobs” and select “New Job.”

2. Create Job for Backup:

   • Name the job (e.g., “Database Backup”).

   • Go to the "Steps" section, click “New,” and add a new step to run the DatabaseBackup stored procedure.

   • Example T-SQL command:

     sql

     Copy

     EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24

3. Schedule the Job:

   • Go to the "Schedules" section and set up a new schedule (e.g., daily at midnight).

4. Repeat for Other Maintenance Tasks:

   • Similarly, create jobs for integrity checks and index maintenance using the respective stored procedures (DatabaseIntegrityCheck and IndexOptimize).

     
     

Step 3: Configure Logging and Monitoring

1. CommandLog Table:

   • The scripts create a CommandLog table to log execution details of each job.

   • You can query this table to monitor the performance and status of your maintenance tasks.

     sql

     Copy

     SELECT * FROM CommandLog ORDER BY StartTime DESC

2. Review Logs:

   • Regularly review the logs to ensure tasks are completed successfully and to identify any issues that need attention.

     
     

Step 4: Customize to Your Needs

1. Modify Parameters:

   • The stored procedures accept various parameters to customize their behaviour. Review the documentation on Ola Hallengren’s website to understand all available options.

2. Create Custom Maintenance Plans:

   • You can create additional jobs or modify existing ones to tailor the maintenance plan to your specific requirements.

   • 
     

By setting up and utilizing Ola Hallengren's maintenance scripts, you can ensure your SQL Server environments are well-maintained with minimal manual intervention. These scripts provide a reliable and efficient way to handle regular maintenance tasks, allowing you to focus on more strategic database management activities.

You can download and utilize Ola Hallengren's SQL Server maintenance scripts from his official website: ola.hallengren.com1. This site provides all the necessary scripts and detailed instructions on how to set them up and use them effectively.

###############################################################

Possible suggested schedule and configaration for your new maintenance plan

Maintenance plan  for onboarding 

Monthly

12:00 CommandLog Cleanup

12:01 Output File Cleanup

12:02 sp_delete_backuphistory

12:03 sp_purge_jobhistory

Sunday

Monday -Sunday          Weekly

Backup 

System Backup 22:55                DatabaseBackup - SYSTEM_DATABASES - FULL

Full Backup 23:00                DatabaseBackup - USER_DATABASES - FULL

Log Backup 30 Minuets / hour                DatabaseBackup - USER_DATABASES - LOG

integrity check 20:00    DatabaseIntegrityCheck - SYSTEM_DATABASES

integrity check 20:30    DatabaseIntegrityCheck - USER_DATABASES

index  01:00                  IndexOptimize - USER_DATABASES

@Directory = '\\SERVER01\SQLBackup\MASSQL02\SYSTEM',

@CleanupTime = '72',

@Compress = 'Y',

Index

@TimeLimit = 3600

email alert set  to  alerts@ME.co.uk

@Verify = 'Y', @CleanupTime = '72', @Compress = 'Y',

DatabaseBackup - SYSTEM_DATABASES - FULL

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'SYSTEM_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Compress = 'Y',

@Verify = 'Y',

@CleanupTime = NULL,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - FULL

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Compress = 'Y',

@Verify = 'Y',

@CleanupTime = NULL,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - LOG

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'LOG',

@Compress = 'Y',

@Verify = 'Y',

@CleanupTime = NULL,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseIntegrityCheck - SYSTEM_DATABASES

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'SYSTEM_DATABASES',

@LogToTable = 'Y'

DatabaseIntegrityCheck - USER_DATABASES

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'USER_DATABASES',

@LogToTable = 'Y'

IndexOptimize - USER_DATABASES

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = 'COLUMNS',

@OnlyModifiedStatistics = 'Y'

###########################################################

Once set up, agent and Alert setup are required to complete your maintenance solution.

I have added a T-SQL to automate the task and remember to change

"incidents@me.co.uk" and "#ME#"

---Agent and alert setup 

--mail operate configuration incidents@me.co.uk

--#ME# Alerts

--iincidents@me.co.uk

USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'#ME# Alerts',

        @enabled=1,

        @pager_days=0,

        @email_address=N'incidents@me.co.uk'

GO

---alert setup

---alert oparator configuerd as '#ME# Alerts'

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',

@message_id=0,

@severity=23,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000';

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',

@message_id=0,

@severity=24,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000';

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',

@message_id=0,

@severity=25,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000';

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',

@message_id=823,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',

@message_id=824,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',

@message_id=825,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'#ME# Alerts', @notification_method = 7;

GO