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! Why reinvent something that does it all, safely and well. After a new SQL Server instance is created, one of the first tasks the DBA must undertake is to create a database maintenance plan.
Ola Hallengren has already crafted a superb set of routines to do this for you.
In addition, Ola’s script includes extensive error-checking to make it easier to troubleshoot any potential problems.
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?
Default they are created in the master database, but you can change it to the msdb database, or to a dba utility database if you prefer that.
The second option 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. If you don’t want the script to automatically create the jobs for you, then replace ‘Y’ with ‘N’.
Ola’s script is designed to cover the most common database maintenance tasks, which include: 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 log files and/or log tables so you can see exactly what maintenance is being performed (even as it is happening).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.When you take a closer look at Maintenance 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: In order to get a better understanding of how the script works, let’s take a look at what each of these sections do.Towards the top of the Maintenance script are five settings that affect how the script will run, and can be optionally changed by directly modifying the script before you run it.Note: The examples provided in this article refer to the Maintenance script that was released on February 17th 2013.