MSSQL Maintenance

Download and run the single install script. This will install default scheduled jobs (but will not schedule them). The default jobs may not be setup as shown below. Review and update as necessary....

ola.hallengren.com

MSSQL Server Agent

DatabaseBackup

DatabaseBackup - SYSTEM_DATABASES - FULL

Run daily
Compression defaults to the setting of 'backup compression default' in sys.configurations

MSSQL Backup

SELECT * FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'SYSTEM_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 48,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - FULL

If total size of all databases is small then run daily
If total size of all databases is large then consider running weekly (in conjunction with DatabaseBackup - USER_DATABASES - DIFF).

Consider the implications to your restore strategy of doing this. Also consider how this impacts any backup of these backup files to another backup tool. i.e. this works best if the other backup tool doesn't backup up the FULL and all DIFF backups every night but backs up only the files that have changed. It does mean restoring more files in order to perform any recovery though.

Compression defaults to the setting of 'backup compression default' in sys.configurations

SELECT * FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'FULL',

@Verify = 'Y',

@CleanupTime = 27,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseBackup - USER_DATABASES - LOG

Schedule hourly (unless there is a good reason to schedule more or less often)
Compression defaults to the setting of 'backup compression default' in sys.configurations

SELECT * FROM sys.configurations WHERE name LIKE '%compression%'

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = NULL,

@BackupType = 'LOG',

@Verify = 'Y',

@CleanupTime = 50,

@CheckSum = 'Y',

@LogToTable = 'Y'

DatabaseIntegrityCheck

DatabaseIntegrityCheck - SYSTEM_DATABASES

Schedule at least weekly

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'SYSTEM_DATABASES',

@LogToTable = 'Y'

DatabaseIntegrityCheck - USER_DATABASES

Schedule at least weekly

EXECUTE [dbo].[DatabaseIntegrityCheck]

@Databases = 'USER_DATABASES',

@LogToTable = 'Y'

IndexOptimize

SQL Server Index and Statistics Maintenance

IndexOptimize - USER_DATABASES

Run weekly (or less frequently).
Don’t use defaults. Consider 50% for REORGANIZE, 80% or even 90% for REBUILD (1).

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 50,

@FragmentationLevel2 = 80,

@LogToTable = 'Y'

UpdateStats - USER_DATABASES

Run daily
This is a new job (not one of the defaults set up by the maintenance solution install script)

MSSQL Server Agent

EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

@LogToTable = 'Y';

Housekeeping Jobs

CommandLog Cleanup

Output File Cleanup

sp_delete_backuphistory

sp_purge_jobhistory

syspolicy_purge_history

Bibliography

https://ola.hallengren.com/https://ola.hallengren.com/sql-server-backup.htmlhttps://glennsqlperformance.com/2021/01/27/updating-your-ola-hallengren-scripts/
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ (1) https://www.brentozar.com/archive/2017/12/index-maintenance-madness/(2) https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlhttps://www.brentozar.com/archive/2013/10/how-much-is-offline-during-an-index-rebuild/https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html