Ola’s SQL Server Maintenance Solution includes various scripts that create jobs and stored procedures once installed on SQL Server. The whole thing is open sourced with the MIT License, which means you can use them at work or even bundle them with paid products.
This script creates some objects in your master system database (by default), and it also creates and enables twelve SQL Server Agent jobs which are used to do things like database backups, index maintenance, and database integrity checks.
Ola Hallengren is a Microsoft Data Platform MVP living in Sweden. He developed the best free maintenance solution for SQL Server, which is widely used and accepted by DBAs worldwide.
Ola Hallengren is a Data Platform MVP and the creator of the “SQL Server Maintenance Solution”.
Components of OLA Hallengren:
- DatabaseBackup: SQL Server Backup.
- DatabaseIntegrityCheck: SQL Server Integrity Check.
- IndexOptimize: SQL Server Index and Statistics Maintenance.
- CommandExecute: Stored procedure to execute and log commands.
- CommandLog: Table to log commands.
How to Install OLA Hallengren?
Installing SQL Server Maintenance Solution:
Step 1: Start by reviewing Ola’s blog post: https://ola.hallengren.com/.
Step 2: Next, download MaintenanceSolution.sql from the blog post link.
Step 3: Since it is a SQL script, it might get blocked by AV software.
Step 4: Keep an eye out for that, and, unblock the script if it does get blocked.
Step 5: Open SQL Server Management Studio (SSMS), connect to your SQL Server.
Step 6: Open the MaintenanceSolution.sql file that you just downloaded.
Step 7: There is only one line that needs to be edited.
Step 8: Adjust the backup directory to where you want to store your backup.
Step 9: In my case this is E:\SQLbak. DECLARE @BackupDirectory nvarchar(max) = ‘E:\SQLbak’ — Specify the backup root directory.
Step 10: Click the Execute button.
Step 11: About a minute later, you should receive this message within the messages window: “Commands completed successfully.”
Confirm SQL Server Maintenance Solution Successfully Installed:
Step 1: There are two places to check.
Step 2: Under the Jobs node, you will find a number of jobs configured.
IndexOptimize – USER_DATABASES
Step 3: In the master database, you’ll see a new table called, dbo.CommandLog.
Step 4: You can query the table by typing Select from dbo.CommandLog.
Step 5: Now that you have confirmed that Ola’s solution is successfully installed and the jobs are created.
IndexOptimize – USER_DATABASES job
How To use Ola Hallengren’s SQL Server Maintenance Solution?
That works just as well as it ever has – he’s really responsive for a guy who must get a gazillion emails, especially all the thank-you emails that you folks are sending him because you rely on his work every day to save your job.
Step 1: Download MaintenanceSolution.sql https://ola.hallengren.com/scripts/MaintenanceSolution.sql.
Step 2: In the script, find this line:
SET @BackupDirectory = N’C:\Backup’
and replace C:\Backup with the path to your backup directory
Step 3: In the script, find this line:
SET @CleanupTime = NULL
and replace NULL with your cleanup time
Execute MaintenanceSolution.sql:
Step 1: Go into [SQL Server Agent] / [Jobs] and start the jobs that have been created.
Step 2: Verify that these jobs are completing successfully.
Step 3: Verify that the backup files are being created.
Step 4: Check the output files in the error log directory.
Step 5: Schedule the jobs.
What is the Purpose of a SQL Server Maintenance Plan?
The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. It allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.