Configure Generic Maintenance Processes

Previous Configure Policy Based Management	Configure Standard Database Properties Next

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

The SQL Generic Maintenance procedures use Ola Hallengren's Database Maintenance procedures for many tasks. Other maintenance processses use procedures that have been developed for SQL FineBuild.

For FineBuild v3.5.0, Database backup, Integrity check and Index maintenance are performed using the 06 December 2020 version of Ola's MaintenanceSolution.sql. If you want to use a more recent version of this file then download it from https://ola.hallengren.com/scripts/MaintenanceSolution.sql and put it into the Additional Components folder.

FineBuild Database Maintenance Processes configuration

The Generic Maintenance Processes configuration relates to Process Id 5EF and is controlled by the parameters below:

SQL Version	Parameter	FULL Build	WORKSTATION Build	CLIENT Build
SQL2019	/SetupGenMaint:	Yes	Yes	N/A
SQL2017	/SetupGenMaint:	Yes	Yes	N/A
SQL2016	/SetupGenMaint:	Yes	Yes	N/A
SQL2014	/SetupGenMaint:	Yes	Yes	N/A
SQL2012	/SetupGenMaint:	Yes	Yes	N/A
SQL2008R2	/SetupGenMaint:	Yes	Yes	N/A
SQL2008	/SetupGenMaint:	Yes	Yes	N/A
SQL2005	/SetupGenMaint:	Yes	Yes	N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter /ConfigGenMaint: can also be used.

FineBuild also uses the following parameters to help configure the SQL Generic Maintenance processes. All of these parameters apply to when a Job is created - if you want to change a value for an existing job then the job itself must be edited.

Parameter	Default Value	Description
/SetBackupRetain:	23	Number of days to keep a Full Backup file before it is deleted
/SetBackupDiffRetain:	23	Number of days to keep a Differential Backup file before it is deleted
/SetBackupLogFreq:	60	Number of minutes between each Log Backup
/SetBackupLogRetain:	24	Number of days to keep a Log Backup file before it is deleted
/SetBackupStart:	21:00:00	Start time of database Full Backup jobs

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.

A list of the maintenance processes is shown below:

Item	Express Edition	Other Editions	Schedule
Backup DB database name	N/A	Agent Job	Daily at /SetBackupStart: time
DB database name: Corruption	N/A	Agent Job	Trigered by an Alert
DBA: Backup DB - All - Full	Windows Task	N/A	Daily at /SetBackupStart: time
DBA: Backup DB - All - Differential	Windows Task	Agent Job	Daily at 07:00
DBA: Backup Log - All Databases	Windows Task	Agent Job	1 minute past midnight, then every /SetBackupLogFreq: minutes
DBA: Backup Log Alert	N/A	Agent Job	Triggered by an Alert
DBA: Cleanup Backup History	Windows Task	Agent Job	Daily at 08:00
DBA: Cleanup CommandLog	Windows Task	Agent Job	Daily at 00:01
DBA: Cleanup Job History	N/A	Agent Job	Daily at 08:00
DBA: Cleanup Output File	Windows Task	Agent Job	Daily at 00:01
DBA: Index Optimise - System Databases	Windows Task	Agent Job	Saturday at 18:00
DBA: Index Optimise - User Databases	Windows Task	Agent Job	Saturday at 18:00
DBA: Integrity Check - System Databases	Windows Task	Agent Job	Daily at 05:00
DBA: Integrity Check - User Databases	Windows Task	Agent Job	Daily at 05:00
DBA: NCCI Maintenance	N/A	Agent Job	Midnight, then every 2 hours
DBA: Set DB Space Used	Windows Task	Agent Job	Saturday at 06:00
DBA: Set DB Maintenance	Windows Task	Agent Job	Daily at 03:00
DBA: Set DB Options	Windows Task	Agent Job	Saturday at 06:00
DBA: Update Stats - User Databases	Windows Task	Agent Job	Saturday at 22:00

The DBA: Set DB Maintenance process creates database backup jobs and database corruption alerts for any recently-added databases. It is scheduled to run every day, 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 FineBuild Team © 2013 - 2021. License and Acknowledgements

Previous Configure Policy Based Management	Top	Configure Standard Database Properties Next