Previous Configure Policy Based Management Configure standard database properties Next

Configure Generic Maintenance Processes

All database maintenance can be performed by the SQL Generic Maintenance processes.

Starting with SQL FineBuild v3.2.0 beta 2, the SQL Generic Maintenance procedures use Ola Hallengren's Database Maintenance solution. Prior to this version, all database maintenance was done using code developed for SQL Finebuild. The old format database maintenance routines are not described further and should be considered as bonus material.

Database backup, Integrity check and Index maintenance are performed using Ola Hallengren's Database Maintenance solution. Other maintenance processses use procedures that have been developed for SQL FineBuild.

FineBuild Database Maintenance Processes configuration

The Generic Maintenance Processes configuration relates to Process Id 5EF and is controlled by the parameters below:
Install Parameter Build SQL2005 SQL2008 SQL2008R2 SQL2012 SQL2014
ConfigGenMaint FULL Yes Yes Yes Yes N/A
ConfigGenMaint WORKSTATION Yes Yes Yes Yes N/A
ConfigGenMaint CLIENT N/A N/A N/A N/A N/A

If SQL Server Express Edition is being installed, the maintenance processes will be set up as Windows Scheduled Tasks, because Express Edition does not include SQL Agent. For all other editions the maintenance processes are set up as SQL Agent Jobs. A core set of maintenance processes is always installed, but the advanced processes can not be installed for Express Edition.

The maintenance processes are currently not available for SQL2014 due to a bug in SQLCMD.

SQL FineBuild uses the 2 March 2013 version of the MaintenanceSolution.sql. If you want to use a more recent version of this file then download it from http://ola.hallengren.com/downloads.html and put it into the Additional Components folder.

A list of the maintenance processes is shown below:
Item Express Edition Other Editions Schedule
Backup DB - All - Full Windows Task N/A Daily at 21:00
Backup DB database name N/A Agent Job Daily at 21:00
Backup DB - All - Differential Windows Task Agent Job Daily at 07:00
Backup Log - All Databases Windows Task Agent Job Hourly, 1 minute past
Backup Log Alert N/A Agent Job Triggered by an Alert
DB database name: Corruption N/A Agent Job Trigered by an Alert
Index Optimise - System Databases Windows Task Agent Job Saturday at 18:00
Index Optimise - User Databases Windows Task Agent Job Saturday at 18:00
Integrity Check - System Databases Windows Task Agent Job Daily at 05:00
Integrity Check - User Databases Windows Task Agent Job Daily at 05:00
Set DB Space Used Windows Task Agent Job Saturday at 06:00
Set DB Maintenance Windows Task Agent Job Saturday at 06:00
Set DB Options Windows Task Agent Job Saturday at 06:00
Cleanup Backup History Windows Task Agent Job Daily at 08:00
Cleanup CommandLog Windows Task Agent Job Daily at 00:01
Cleanup Job History N/A Agent Job Daily at 08:00
Cleanup Output File Windows Task Agent Job Daily at 00:01

The Set DB Maintenance process creates any missing database backup jobs and database corruption alerts. It is scheduled to run every Saturday, but can also be run manually at any time to create the backup job for a new database.

After all processes have been created, review the scheduled run times so they meet your requirements. Except for Express Edition Full Backup jobs are created for each database, and it can be worth adjusting the schedules of these so that only about 4 jobs run at the same time in order to avoid saturating memory or disk IO.

Copyright © 2013 Edward Vassie. License and Acknowledgements
Previous Configure Policy Based Management Top Configure standard database properties Next

Last edited Aug 8, 2013 at 4:32 PM by EdVassie, version 4

Comments

No comments yet.