page contents

About the Post

Author Information

OLA Recommendations for his script

In an earlier article, I spoke about Ola Hallengren’s highly recommended database maintenance scripts. Today, I want to talk a little bit more about configuring his script in hopes that you don’t just download it and click execute without making some changes.

Before I even think about executing Ola’s maintenance script, I configure SQL server to turn on backup compression by default. This step is done to save disk space and I also turn on the advanced options of sp_configure: (Steps below)

— Change backup compression to be on

sp_configure ‘backup compression default’, 1 reconfigure

–Show advanced options 

EXEC sp_configure ‘show advanced option’, ‘1’

RECONFIGURE

GO

— Check settings

sp_configure

It will return these results when you are done: (1 is on and 0 is off)

Before you begin modifying Ola’s scripts, I would suggest that you read the SQL Server backup and Frequently Asked Questions sections. It will help you determine what to configure for your business needs. It is important to point out that the recommended order of the jobs is to do the index maintenance, then integrity check, and finally the backups. (I will point these out again)

So right off the bat, let’s look at the first area where we need to make changes in the script. The location of the backups and here is the default location: (Click on image for larger view)

I changed mine to reflect where I wanted to back up to a Z drive and you will need to ensure the modified path exists: (Click on image for larger view)

Then the next thing, I looked for was the retention period of backups and make sure that you meet your requirements. I noticed this comment in the SQL Server backup section

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted. Backup files are deleted after each database is backed up and verified. Backup files are deleted only if the backup and verification of the database were successful.

Here are the default settings: (Just search for “checksum” using CONTROL-F)

I made changes to the backup cleanup time and I turned off the verify command: (Keep in mind what was said earlier which can impact your retention period: Backup files are deleted after each database is backed up and verified.)

In this scenario, we can allow for a backup to be alive for a certain amount of time in hours and as long as we are not verifying the backup. For example, we can specify that we want a week for our retention period by selecting the Cleanup Time to be 168 hours (24 x 7) and then leaving Verify set to N. (As seen below)

The last thing I do is order the steps. As stated earlier, the recommended order of the jobs is to do the index maintenance, then integrity check, and finally the backups.

The default settings are seen here:

To change them follow these steps:

  • Search (Control –F) for DatabaseBackup – SYSTEM_DATABASES – FULL


  • Label the Index Job step 1
  • Label your Integrity checks as the next 2 steps
  • Label the other jobs accordingly

NOTE: The above 3 steps are done to help you configure the scheduling easier in SQL Agent jobs.

Please note that Ola makes these recommendations for frequency of running the jobs:

User databases:

  • Full backup one day per week
  • Differential backup all other days of the week
  • Transaction log backup every hour
  • Integrity check one day per week
  • Index maintenance one day per week

System databases:

  • Full backup every day
  • Integrity check one day a week

Cleanup:

  • sp_delete_backuphistory one day a week
  • sp_purge_jobhistory one day a week
  • CommandLog Cleanup one day a week
  • Output File Cleanup one day a week

When you are done following his recommendations and my suggestions to number the steps, then it should look like this:


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When you execute the script, it will create the jobs and as you will see later on, the numbering will help us in scheduling the jobs:

Please note that all of these jobs are configured with no schedules, so you will need to create one for them. Here is how the jobs look by default under schedules:

The last step is to configure the jobs to run with either his recommendations or modifying those recommendations to fit your needs. I have swapped out all of my old scripts in favor of his scripts and I have been really impressed with the results.

As usual, I hope this information helps you.

Tags: , , , , ,

3 Responses to “OLA Recommendations for his script”

  1. Steve #

    Hi

    Thanks for your article.

    1.
    You say that you turned off the verify command – and you had a link to Ola’s page.

    I think you misunderstood Ola’s page, which I think means

    “After each database is successfully backed up (and verified if that has been requested), the old backup files are deleted”

    ie the deletion step happens after the backup and verification step. The deletion will only delete the backup files that have been expired (as per the cleanuptime) – it does not delete the backup files that have just been created and verified.

    Grant Fritchey and others encourage people to Verify their backups.

    2.
    Rather than edit the scripts, you can supply parameters to the stored procedures when you call it from a scheduled job.
    ie
    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘USER_DATABASES’, @Directory = N’F:\SQL Backup\’, @BackupType = ‘FULL’, @Verify = ‘Y’, @Compress = ‘Y’, @CleanupTime = 168, @CheckSum = ‘Y’ , @LogToTable = ‘Y’ ” -b

    That way if you download an upgraded script from Ola, you won’t have to reedit it and your scheduled job will still work.

    Hope I have got it right.

    Regards
    Steve
    London UK

    June 27, 2013 at 8:44 AM
    • Scott Mattie #

      Thanks Steve for the tip 🙂

      June 27, 2013 at 11:40 PM

Trackbacks/Pingbacks

  1. Update to an earlier article... Ola Recommendations for his script | Scott Mattie's Blog - September 26, 2012

    […] […]

Copy Protected by Chetan's WP-Copyprotect.