Every time I’ve installed new SQL Server instance, one of my first task is to create database maintenance plan. Benefits from implementing proper database maintenance plan is to help to prevent or at least to catch early and database problems.
First time I know about this SQL Server, I’m using SQL Server Maintenance Plan Wizard to create database maintenance plan from SQL Server Management Studio. But after a while, some experts said that this tool is not very flexible and if it isn’t properly used it can result in poor database maintenance.
For more experienced DBA, they create customs T-SQL script to perform their database maintenance plans, but I’m not that experienced. I need customs free script that widely used over the world of DBA’s, and finally I’ve found this Olla Hallengren’s script. It answer all I need for my Database maintenance tasks. This script was designed to cover all aspects of database maintenance tasks that I need which include :
- Full, Differential, and Transaction Log backup of databases
- Dynamic index maintenance by rebuilding or reorganizing indexes and updating statistics
- Check database integrity check
- Delete old backup job history including removing log files created by maintenance jobs
- Automatically create job and implement each maintenance tasks.
The installation of this script is really simple. I always create a database for maintenance purposes, so I installed this script against that database. By open this script using SSMS, there are few things I need to adjust depend on our maintenance plans as following :
SET @BackupDirectory = N'F:\DB_BACKUP' SET @CleanupTime = 672 SET @OutputFileDirectory = N'F:\MaintenanceLog'
After change those three rows, execute that script and it will automatically create the following :
- CommandLog table
- CommandExecute stored procedure
- DatabaseBackup stored procedure
- DatabaseIntegrityCheck stored procedure
- IndexOptimize stored procedure
- Creating maintenance jobs
All I need to do after installing this script is only setup the schedule for each job depending on our maintenance plans as follows :
- CommandLog Cleanup (Weekly Sunday – 04:50)
- DatabaseBackup – USER_DATABASES – FULL (Weekly – Sunday – 03:00)
- DatabaseBackup – USER_DATABASES – DIFF (Weekly – Mon/Tue/Wed/Thu/Fri/Sat – 03:00)
- DatabaseBackup – USER_DATABASES – LOG (Hourly – 12:20 )
- DatabaseBackup – SYSTEM_DATABASES – FULL (Daily – 04:35)
- DatabaseIntegrityCheck – USER_DATABASES (Weekly – 02:00)
- DatabaseIntegrityCheck – SYSTEM_DATABASES (Weekly – 04:00)
- IndexOptimize – USER_DATABASES (Weekly – 00:00)
- Output File Cleanup (Weekly – 04:55)
- sp_delete_backuphistory (Weekly – 04:40)
- sp_purge_jobhistory (Weekly – 04:45)
All this job has created with default parameters but I updated few of them especially for IndexOptimize job to add @UpdateStatistics option. Also I need to setup an alert when a job has failed. I’m using this script for long times now and I’m really satisfied with this script. Thank you.