I find OLA HALLENGREN’s maintenance script very successful and practical.
In this article I will tell you where to download and install the script that OLA shared for maintenance.
Download And Create
After downloading the MaintenanceSolution.sql file on the link below, you should run the script on your instance and schedule the jobs created.
I am using DatabaseIntegrityCheck (run dbcc checkdb on all databases) and IndexOptimize (rebuild or reorganize indexes on all databases in intance according to some criteria).
I recommend using a professional backup software for Backup.
Monitoring Created Jobs
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Right-click on the Job Activity Monitor under SQL Server Agent and click View Job Activity.
You can see which jobs are created on the right side. I prefer to delete jobs that are created for Database Backup.
Configure Database Integrity Check
For other jobs,
I configure the “DatabaseIntegrityCheck – SYSTEM DATABASES – FULL” job to run every day as follows.
To configure it, you should right click on the relevant job and click Properties.
Then you should come to the schedule tab and click on new. The display comes as follows.
In the Name section you should a name to the schedule. I gave the daily name.
Schedule type should be recurring. You should choose Daily from Occurs.
In the “Occurs once at” section, you also set the time the job will work.
Click OK to schedule the job.
I will schedule the “DatabaseIntegrityCheck – USER DATABASES – FULL” job to run once a week.
I can not run DBCC CHECKDB everyday because my databases are large.
If your databases are small, you can set this job to work every day.
To schedule the Job to run once a week, you should select Weekly from Occurs on the above screen.
Configure INDEX OPTIMIZE JOB
I schedule the “INDEX OPTIMIZE – USER DATABASES” job to run once a week.
When you create the script, you will see that CommandExecute, DatabaseBackup, DatabaseIntegrityCheck and IndexOptimize stored procedures are created under the master database.
By right clicking on these stored procedures you can set the relevant parameters.
I do not recommend taking Backups with this script.
That’s why you can delete the stored procedure called DatabaseBackup.
You do not need to make any changes to the stored procedure named DatabaseIntegritCheck.
Right click on the stored procedure named IndexOptimize and we click Modify and we set the fill factor to 90 as below and update the SP by pressing F5.
You can bypass this process for smaller indexes by increasing the value of the @PageCountLevel parameter, but I do not need it. The rebuilding of small indexes is already short.
The @ FragmentationLevel1 and @ FragmentationLevel2 parameters are used to determine which index will be reorganized, which index will be rebuilt.
In our example, values of 5 and 30 are given.
This means that;
Reorganize if the fragmentation rate is between 5 and 30, and rebuild if it is greater than 30.
If you set @UpdateStatistics parameter to ALL, it updates the statistics of index and column.
I recommend that you use it like this, or set this parameter to null and set “@OnlyModifiedStatistics” to ‘Y’.
In this way, it will only update the relevant statistics if there has been a change since the last update.
Under the master database, a table called CommandLog keeps logs about whether the maintenance operations on the job are working correctly.
