If you ever feel uneasy when you set about handcrafting database maintenance jobs for SQL Server, it may be the voice of your conscience saying 'Ola, Ola!'. Follow your conscience. Ola Hallengren has already crafted a superb set of routines to do this for you. Why reinvent something that does it all, safely and well. Brad McGehee explains....

After a new SQL Server instance is created, one of the first tasks the DBA must undertake is to create a database maintenance plan. If implemented correctly, a database maintenance plan can help ensure that a SQL Server's databases perform optimally and, if there should be a problem, provide the necessary backups to minimize the loss of any data. Another benefit of implementing a database maintenance plan is that it helps to prevent, or at least to catch early, many different kinds of database-related problems. By being proactive with a good maintenance plan, time spent troubleshooting problems after the fact is often reduced.

One of the most common ways that DBAs (especially accidental or novice DBAs) create database maintenance plans is to use the Maintenance Plan Wizard from within Management Studio (SSMS). While it is possible to create a decent database maintenance plan using the Maintenance Plan Wizard (see my free eBook: Brad’s Sure Guide to SQL Server Maintenance Plans), the tool is not very flexible, and if it isn’t properly used, it can result in poor database maintenance.

Another common way for more experienced DBAs to create database maintenance plans is to create custom T-SQL or PowerShell scripts to perform database maintenance. These, often carefully-crafted and fine-tuned scripts, are often the ideal way to perform database maintenance because they can be customized to meet particular needs. Given these choices, which option should the DBA pick to create their database maintenance plans? If you are an experienced DBA with a lot of Transact-SQL development experience, and you have spare time, then creating your own custom database maintenance plan is a common solution. On the other hand, there are a lot of experienced DBAs who lack the time, and there are many novice DBAs who don’t have the knowledge or experience, to create their own custom scripts.

So does this mean that if you can’t create your own custom scripts that your only option is to use the Maintenance Plan Wizard? Fortunately, this is not the case, because there are many DBAs who have written generic database maintenance scripts that are much more flexible and powerful than those created with the Maintenance Wizard. In this article, we are going to take a look at a Transact-SQL database maintenance script written by Ola Hallengren, which not only offers great flexibility to meet the needs of most any database (SQL Server 2005 and newer), it is easy to implement, even by inexperienced DBAs.

Note: I am assuming that the readers of this article understand the basics of database maintenance, so the focus of this article is strictly on how the script works, not on explaining database maintenance best practices.

Who Is Ola Hallengren

Ola Hallengren is a DBA and database developer who is currently working with a large financial company in Sweden. He has been working with SQL Server since 2001.

Back in 2007, Ola began a project to improve the database maintenance within a large SQL Server environment. At that time, a combination of database maintenance jobs created using the SQL Server Maintenance Wizard, along with a collection of custom Transact-SQL scripts, were used throughout the organization. His goal was to remove the hodgepodge collection of jobs and to create an easy-to-deploy solution that could be easily rolled out across a large environment of mission critical servers.

After the project was completed, Ola realized that his solution would be useful to other DBAs, so starting January 1, 2008, Ola released his first version of his database maintenance script, offering it free to any organization who wants to use it. Since then, he has improved his script on a regular basis, and it is now being used by some of the largest organizations in the world.

His current version was released July 20, 2010, and you can download and read the documentation, at his website ola.hallengren.com.

Overview

Ola’s script is designed to cover the most common database maintenance tasks, which include:

• The ability to performing full, differential, or log backups (as appropriate) of any system and user databases, using either the native SQL Server backup command, or some third-party backup products, such as SQL Backup, or SQL HyperBac. The script also has the ability to automatically remove older backup files from disk that are no longer needed.
• The ability to perform dynamic index optimization by rebuilding indexes online or offline (depending on the edition of SQL Server you have), reorganizing indexes, updating statistics, or doing nothing at all, based on an index’s size and fragmentation level.
• The ability to perform database integrity checks using DBCC CHECKDB.
• The ability to delete old job and backup history, including the removal of log files created by maintenance jobs as they run.
• The ability to automatically create jobs that implement each of the above tasks. All you have to do is to decide when to run them and schedule them appropriately. Alternatively, you can specify that jobs are not automatically created, so you can create your own.

Each of the above features can be run with default settings, or various parameters can be changed to customize how the maintenance tasks run on your SQL Server instances. As each job is run, the results are logged immediately to disk so you can see exactly what maintenance is being performed (even as it is happening). In addition, Ola’s script includes extensive error-checking to make it easier to troubleshoot any potential problems.

How to Use Ola’s Maintenance Script: The Simple Explanation

If you are a novice DBA, or maybe you are just in a hurry to get a maintenance plan running on your SQL Server instances, you can quickly get Ola’s maintenance plan implemented. Here’s how:

1. Download the MaintenanceSolution.sql script from Ola’s website and open it up in a query window inside of SSMS.
2. Near the top of the script, around line 28, you will see a line of Transact-SQL code that looks like this:
   
   
   SET @BackupDirectory = N'C:\Backup' -- <== Change this to your backup directory.
   
   
   Replace C:\Backup' with the pathname of the location where your backups are to be stored.
3. Run the script. At this point, one function and four stored procedures are created in the master database, along with 10 new jobs pre-created to perform all the maintenance tasks described earlier.
4. Manually schedule the pre-configured jobs to run at appropriate times.
5. You are now done.

As you can see, Ola’s maintenance tasks can be very simple and quick to implement. But like most DBAs, I don’t like to run scripts, even professionally polished and written scripts such as Ola’s, without taking some time to fully understand how the script works. In the next section, we dissect Ola’s script so that we better understand how it works. At the same time, we will learn how it can be customized for your specific environment.

How to Use Ola’s Maintenance Script: The Complete Explanation

The best way to understand how the MaintenanceSolutions.sql script works is to take a detailed look at it. To do this, download the script and open it up in a query window of SSMS, and follow along as I discuss it.

When you take a closer look at MaintenanceSolution.sql script, you will see that it is divided into essentially seven different sections (I have arbitrarily divided the script into seven sections to make it easier to describe), which include:

1. Essential settings
2. Creating the DatabaseSelect function
3. Creating the CommandExecute stored procedure
4. Creating the DatabaseBackup stored procedure
5. Creating the DatabaseIntegrityCheck stored procedure
6. Creating the IndexOptimize stored procedure
7. Creating the Maintenance Jobs

In order to get a better understanding of how the script works, let’s take a look at what each of these sections do.

Essential Settings

Towards the top of the MaintenanceSolutions.sql script are two settings that affect how the script will run, and can be optionally changed by directly modifying the script before you run it. I described one of the options in the previous section, but there is another setting that you may want to change.

Figure 1: While you must change the script for the first setting, changing the second setting is optional.

In the above figure, you can enter the location where you want your backups to be stored. As I previously mentioned, all you have to do is to replace the C:\Backup' with the appropriate path. This setting is only required if you are also having the script create the maintenance jobs for you (discussed next), as this path is used as the location of the backup files for the backup jobs that are created. If you don’t intend to use the jobs created by the script, then this setting is irrelevant, as you can add a backup location as a parameter of the DatabaseBackup stored procedure. More on this later.

The second option in figure 1 above is used to tell the script if you want it to automatically create SQL Server Agent jobs for you that are pre-configured, and ready to run, although they still need to be scheduled. The default is 'Y', or yes. If you don’t want the script to automatically create the jobs for you, then replace 'Y' with 'N'. Personally, I prefer to create my own jobs manually in order to have more control over how they work, and that’s how I have implemented Ola’s script in the past. If you are like me, and prefer to create your own custom jobs, I still suggest that you have the script create the maintenance jobs for you, as they act as great templates you can follow when you create your own customized jobs. Once you are done looking at the jobs created by the script, you can later delete them and create your own.

Other than these two potential changes, the script does not have to be modified (although you have that option if you want to customize the script). After you have made any changes to the script, you can run the script, at which point the DatabaseSelect function; along with the CommandExecute, DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize stored procedures are created in the master database. In addition, if you chose to have the script create them, the pre-configured maintenance jobs will be created as well. Let’s take a deeper look at each of these objects.

DatabaseSelect Function

This section of the script creates a function called DatabaseSelect, which is used to make it easier for you to select which databases you want to perform maintenance tasks on.

Figure 2: The DatabaseSelect function makes it easy for you to select which databases to perform maintenance on.

When you implement the various maintenance tasks performed by the script, this function allows you to select from these options:

• All databases
• All system databases
• All user databases
• A single database, specified by its name
• Multiple databases, specified by their names
• All databases, except a database, which is specified by its name
• All databases that have meet a wildcard match, such as %partial_database_name%
• All databases that have meet a wildcard match, such as %partial_database_name%, except for a database with a specified name.
• All databases that do not match a particular wildcard match, such as %partial_database_name%

As you can see, this is more than enough options to select which database(s) you want to perform a specific maintenance task on. You select one of the above options when you (or a scheduled job) executes the appropriate stored procedure to perform the maintenance task. This will be demonstrated later.

CommandExecute Stored Procedure

This stored procedure is used internally by the three main maintenance task stored procedures, which are discussed next. Think of this stored procedure as a subroutine that is required for the other stored procedures to work. Its purpose is to ensure consistent error handling and logging across the DatabaseBackup, DatabaseIntegrityCheck, and IndexOptimize stored procedures. CommandExecute is actually a useful general way of executing strings of TSQL commands. It records each call, together with the time it was done, to the SQL Server log, and, once the command is executed, it logs whether it was successful and how long it took.

Figure 3: The Command Execute stored procedure is for internal use only.

DatabaseBackup Stored Procedure

Now we get to the stored procedure that is designed to perform backup-related maintenance tasks. This stored procedure accepts up to 16 different parameters that affect the stored procedure’s behavior. In figure 5 below, you see the available parameters. In figure 6 below, you see the available options for each of the parameters.

Figure 5: The DatabaseBackup stored procedure offers many options to control how backups are made.

Figure 6: This table explains all of the backup options you can set.

To implement any of the 16 options, you provide the appropriate parameters when the stored procedure is executed as a job. We will see an example of this later in the article. One key thing to remember about these parameters is that most of them have default values, so you don’t have to provide every parameter. For example, only the first three parameters are required for the DatabaseBackup stored procedure, although you may want to use more than just the three required parameters.

DatabaseIntegrityCheck Stored Procedure

As you have probably guessed, this stored procedure is designed to check the integrity of your databases. This stored procedure only has five options, making it easier to configure than the previous one. In figure 7 below, you see the available parameters. In figure 8 below, you see the available options for each of the parameters.

Figure 7: The DatabaseIntegrityCheck stored procedure offers five different ways to control how database integrity checks are performed.

Figure 8: This table explains all of the database integrity checking options.

As with the previous stored procedure, to implement any of the five options, you provide the appropriate parameters when the stored procedure is executed as a job. For this stored procedure, only the first parameter is required.

IndexOptimize Stored Procedure

Of all the maintenance stored procedures, the IndexOptimize is the most complex to configure with a total of 18 different parameters. On top of this, some of the options are not very intuitive. In figure 9 below, you see the available parameters. In figure 10 below, you see the available options for each of the parameters.

Figure 9: The IndexOptimize stored procedure offers many options to control how your database’s indexes are optimized.

Figure 10: This table explains all of the index defragmentation options.

To implement any of the 18 options, you provide the appropriate parameters when the stored procedure is executed as a job. For this stored procedure, only the first parameter is required, while all of the rest have default values.

While most of the parameters should be familiar to many DBAs, the following parameters and their settings may appear to be a little confusing.

Figure 11: The above parameters may not be obvious as to what they do.

The parameters listed in figure 10 above include two significant parts. First, you will notice that each of them has either, High, Medium, or Low in their name, and this refers to the relative amount of fragmentation an index can have. Second, you will notice that each of them has either LOB or NonLOB in their name, and as you might guess, this refers to indexes that contain (LOB) or not contain (NonLOB) LOB columns.

For example, let’s take a look at the @FragmentationHigh_LOB parameter first. This parameter applies to those indexes in a database that have high fragmentation and contain LOB data. On the other hand, the @FragmentationLow_NonLOB parameter applies to those indexes in a database that have low fragmentation and no LOB data. While I think it is obvious what LOB and NonLOB mean, the meaning of High, Medium, and Low are not so obvious. So, what does High, Medium, and Low mean in the context of these parameters?

High, Medium, and Low index fragmentation are defined by two of the other available parameters: Fragmentationlevel1 and FragmentationLevel2. By default, the value for FragmentationLevel1 is set to 5 (5%). This means that any index that has index fragmentation below 5% is considered to have Low fragmentation. The default value for FragmentationLevel2 is 30 (30%) and means that any index that has a fragmentation level of 30% or more is considered to have High fragmentation. This leaves us with the level of fragmentation that is 5% and above and below 30%. This is considered Medium fragmentation. So High, Medium, and Low fragmentation levels are defined by other parameters, and can be changed if you like.

Now that you have a better understanding of what the various parameters in figure 11 represent, let’s now discuss what the values for the parameters mean, such as INDEX_REBUILD_OFFLINE. A description of each of the parameter values are found in figure 12 below.

Figure 12: These parameter values describe the type of defragmentation to perform on an index based on its fragmentation level (and LOB) status.

In figure 11 above, notice that the parameter @FragmentationHigh_LOB is assigned the value of INDEX_REBUILD_OFFLINE. This means that if an index has high fragmentation and a LOB column, then the INDEX_REBUILD_OFFLINE option is used. If you look up this parameter value in figure 12, it says that INDEX_REBUILD_OFFLINE specifies that the index will be rebuilt offline.

All the default values in figure 11 above can be changed if you like. For example, while @FragmentationHigh_NonLOB is by default assigned the value of INDEX_REBUILD_OFFLINE, you can change this to INDEX_REORGANIZE if you prefer. You can mix and match the various parameter values as you see fit, although the default values are probably the best choices in 99% of the cases.

So what’s the point of all the complexity? The point is that the IndexOptimize stored procedure has the ability to view the fragmentation level of every index in a database and to classify it as any of the parameters in figure 11. Once this classification is done, then, on an index-by-index basis, the appropriate action is taken on the index so that it is optimized using the fewest amount of server resources possible. For example, if index fragmentation is High, then it might be rebuilt. If the index fragmentation is Medium, then it might be reorganized and its statistics updated. If the index fragmentation is Low, then nothing may be done to the index.

The ability of the IndexOptimize stored procedure to treat each index differently is a great benefit as it helps to achieve a good balance between the level of index optimization performed and the amount of resources needed to perform the optimization, and at the same time helping to minimize the size of the transaction log, which is affected whenever indexes are defragmented. This is also a lot more resource efficient that treating all indexes the same when they are optimized.

The only required parameter for the IndexOptimize stored procedure is the first one, which specifies which database(s) you want the maintenance performed on. All of the other settings have default values and should meet the needs of most database environments.

Creating the Maintenance Jobs (and Cleanup Jobs)

Assuming that you specified at the beginning of the Maintenance Solution.sql script that you want it to create maintenance jobs, running the script will create a series of ten jobs, which include:

• DatabaseBackup - USER_DATABASES – FULL
• DatabaseBackup - USER_DATABASES – DIFF
• DatabaseBackup - USER_DATABASES – LOG
• DatabaseBackup - SYSTEM_DATABASES – FULL
• DatabaseIntegrityCheck - USER_DATABASES
• DatabaseIntegrityCheck - SYSTEM_DATABASES
• IndexOptimize - USER_DATABASES
• Output File Cleanup
• sp_delete_backuphistory
• sp_purge_jobhistory

All of these jobs have been created with default parameters from the script, but you can change the parameters, if you want to, directly from within the jobs themselves. In addition, none of the jobs have been scheduled, because you, as the DBA, must decide what jobs you want to run, and when, in order to minimize the performance impact these jobs can have when they execute. While these ten jobs have been created, they all may or may not need to be used, depending on your SQL Server environment. This means that you may end up modifying or deleting some of these jobs.

Before we take a quick look at each of the jobs, we first need to briefly discuss how Ola’s script creates jobs. Once we have this basic understanding, then we will be better prepared to understand how they execute.

All of the jobs created by Ola’s scripts run use the sqlcmd utility, which allows Transact-SQL scripts to be executed from the command line. This makes it easy to create and schedule your jobs. On the other hand, if you have not used the sqlcmd utility before, it may take you a little time before you fully understand how it works. Since this is not an article on how to use the sqlcmd utility, I suggest you look it up on Books Online if you are not familiar with it.

Another feature that Ola uses in his jobs are tokens, another topic that many DBAs may not be familiar with. In short, a token is similar to an @@function in SQL Server. When you place a pre-defined token inside a job step script, when the script is executed, SQL Server Agent replaces the token at run time. For example, the token $(ESCAPE_SQUOTE(JOBID)), at runtime, will insert the value of the jobid of the job into the script. To learn more about tokens, look up “Using Token in Job Steps” in Books Online.

For the moment, if you are not familiar with the sqlcmd utility or tokens, don’t worry, as I will be showing an example of how they are used as I explain how the first maintenance job works. While you won’t become an expert on using these from this one example, you should learn enough from it to understand what is going on.

Let’s take a quick look at each of the jobs and see how they work. Once you have done so, you can decide if you want to use them as is, modify them, or ignore then altogether and create your own custom jobs.

As you can guess from the name of this job, this job’s purpose is to create full backups of user databases. Generally speaking, I like to make full backups of all my databases nightly. This job includes a single job step, as you can see below.

Figure 13: Each job created by the script creates a single step.

When I click on the Edit button, we can view the job script for this particular job.

Figure 14: Every job created by the script is executed as an operating system (CmdExec) job.

Each of the jobs created by the MaintenanceSolution.sql script creates a single step, and that step executes as an operating system (CmdExec) job. The command is too small to read in figure 14 above, so here is the text of the command for this job.

The above command combines both sqlcmd utility and job token syntax. Let’s break down this command into its constituent parts.

At first glance, this appears complicated. But once you spend some time reviewing this code, and the code found in the other jobs, it should start to make sense.

Now that the job code has been reviewed, we now need to take a look at one more part of the job, and that is the Output File option of the Advanced Tab, which is shown below.

Figure 15: The Output File option is where the maintenance job’s log file goes.

The output file for a job is important because it shows you exactly what the job did (which I suggest you regularly review), which can be useful for troubleshooting. I want to talk about it here because the script uses a lot of tokens in the output file path. Since we can’t read the path in figure 15, here is what it look like.

As with the job code, this output path may seem overly complicated, but is actually quite clever as it uses tokens to create a unique log filename for every job that runs, which makes it very easy to find the log files of any jobs you are interested in viewing.

Here’s a breakdown of the output path’s constituent parts:

When the above job is executed, the output path may create a result like this:

While the job id and the step id are obscure, they help to make the filename unique. When I look at output files, what I focus on is the “DatabaseBackup” part, which tells me what job was run, and the date and time, so I know when it ran.

If you plan to use this job, you still have one mandatory step you must take, and several optional steps. The mandatory step is that you must schedule this job, as this is not done for you. The optional steps are to set up Alerts and Notifications, which I highly recommend, so that you are notified in case a job fails. Of course, you may also want to modify the job in other ways by changing the stored procedure’s parameters, but I’ll leave that up to you.

Of course, you don’t have to use this job. As I mentioned earlier, I prefer to create my own jobs, but I have used the jobs created by the script as a template for my own jobs.

Now that we have a reasonably good knowledge of how jobs created with the script work, now let’s take a brief look at the remaining jobs, but without all the detail.

DatabaseBackup - USER_DATABASES – DIFF

This job is designed to create differential backups of your user databases. If you do create differential backups, be sure that you schedule this job to run after a full backup of your databases, and schedule the backups appropriately. On the other hand, if you don’t create differential backups, you can delete this job.

DatabaseBackup - USER_DATABASES – LOG

This job is designed to create log backups of all user databases that use the full or bulk logged recovery models. Be sure to schedule this job so that it runs after a full backup. Generally speaking, I schedule my log backups to occur every hour (or more often), depending on my needs.

DatabaseBackup - SYSTEM_DATABASES – FULL

This job creates full backups of the system databases, and generally speaking, this job should be scheduled to run nightly. There is no transaction log backup job created by the script for system databases because they use the simple recovery model, and transaction log backups cannot be taken of them. Some DBAs prefer to create a single job to perform full backups of user and system databases at the same time. You can do this if you want, but you will have to modify the jobs accordingly, as the script does not provide this option.

DatabaseIntegrityCheck - USER_DATABASES

This job performs a DBCC CHECKDB on all the user databases, looking for potential database corruption. Be aware that this job could be time and resource consuming, so schedule it during less busy time of the day.

DatabaseIntegrityCheck - SYSTEM_DATABASES

This job performs a DBCC CHECKDB on all the system databases, looking for potential database corruption. Because the system databases are generally small, running this job should not take long. Some DBAs prefer to create a single job that runs DBCC CHECKDB on both user and system databases at the same time. You can do this if you want, but you will have to modify the jobs accordingly, as the script does not provide this option.

IndexOptimize - USER_DATABASES

This job analyzes all of the indexes in all of your user databases, and based on the size of the indexes, and the fragmentation level of the databases, determines if the index should be ignored, reorganized, or rebuilt. Of all the jobs created by the script, this is one that you may want to alter, changing some of the IndexOptimize stored procedure parameters so you have better control over how this job runs. Of course, the default options will work well for most SQL Server instances if you don’t have the time or knowledge to tweak the parameters. Notice that there is no job created for index optimization for the system databases. This is because index optimization for system databases seldom is beneficial, although you can create a job to perform this task if you like.

Output File Cleanup

This job simply deletes log files created by the various database maintenance jobs that are over 30 days old. If you want to change the 30 day value, you can do so by directly editing the command line for the job, changing it from 30 to whatever number of days works best for you. You may choose to run this job daily, weekly, or monthly.

SP_delete_backuphistory

This job deletes backup history from the msdb database that is over 30 days old using the system stored procedure sp_delete_backuphistory. If you want to change the 30 day value, you can do so by directly editing the command line for the job, changing it from 30 to whatever number of days works best for you. You may choose to run this job daily, weekly, or monthly.

SP_purge_jobhistory

This job deletes job (SQL Server Agent jobs) history from the msdb database that is over 30 days old using the system stored procedure sp_purge_jobhistory. If you want to change the 30 day value, you can do so by directly editing the command line for the job, changing it from 30 to whatever number of days works best for you. You may choose to run this job daily, weekly, or monthly. While the script creates separate jobs for the sp_purge_jobhistory, sp_delete_backuphistory, and the Output File Cleanup jobs (as we have just seen), I prefer to combine all three of these maintenance tasks into a single job, as all three jobs are closely related and can easily be scheduled to run at the same time. If you want to do this, you will have to alter one of the three jobs so that it performs all three tasks, and then delete the remaining two you don’t need, as the script does not provide this option.

Job Summary

Now that you have had a chance to review the jobs that have been created by the script, and have learned how they work, you should be in a better position to determine if you want to use these jobs as is, to modify them, or to create your own. Each of these options work, and it is up to you as the DBA to make the choice that will work best for your environment.

Keeping Up-To-Date

Periodically, Ola updates his scripts, fixing small bugs or adding new features. If the current version of Ola’s script you are using works great for you, then you probably don’t need to upgrade when he updates his script. On the other hand, if he introduces a new feature you want to use, then upgrading is easy. Assuming you have not customized his original script, you can run the new script as if it was a new install.

When you run the new script, Ola guarantees backward compatibility. If you modified any of the jobs created by his script, they are left alone by the upgrade process. If you have created your own custom jobs, they will also continue to work without modification.

If you have modified his script directly (other than the two essential settings), you might want to consider contacting Ola to find out if the customizations you have made are still needed in the new version.

Summary

Although this has been a long article, it still does not cover every option in great depth. I have made the assumption that you understand what the various parameters do, or at least that you can look them up. I have also assumed that you understand how to properly schedule your database maintenance jobs. So at this point, if this article has sparked an interest in Ola’s script, then download it and install it on a test server and try it out. Once you are satisfied that it works as you expect, then roll it out to your SQL Server instances and begin to immediately reap the benefits Ola’s script provides. If you have specific questions about how Ola’s script runs, or want to suggest new features, contact him through his website and ask him directly, as he is the expert on the script.