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.
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.