Maintenance Plans
Overview
The DB maintenance routines are based on the well known scripts set of Ola Hallengren.
For the MVP the solution will be implemented with fixed scheduling and covering all the database.
Integrity check
Details about syntax and options can be found under: https://ola.hallengren.com/sql-server-integrity-check.html.
The jobs are implemented with the following command.
System databases
EXECUTE dbo.sp_DatabaseIntegrityCheck
@Databases = 'SYSTEM_DATABASES',
@CheckCommands = 'CHECKDB',
@LogToTable = 'Y'
Use in Job: SCS_SysDbIntegrity
User databases
EXECUTE dbo.sp_DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@LogToTable = 'Y'
Use in Job: SCS_UserDbIntegrity
Index and statistics maintenance
Details about syntax and options can be found under: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.
In the reindexing job we include an additional update statistics for modified rows only.
The sort operations are redirected to the tempDB as we have a dedicated drive. Furthermore we put a time limit of 5 hours.
Reindexing
EXECUTE dbo.sp_IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@OnlyModifiedStatistics = 'Y',
@TimeLimit = 18000,
@LogToTable = 'Y'
Use in Job: SCS_Reindexing_Statistics
Update Statistics
EXECUTE dbo.sp_IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@LogToTable = 'Y'
Use in Job: SCS_AllUserDbUpdateStats
Cleanup
The cleanup is implemented at two level:
- Table T_CommandLog: job SCS_CommandLogCleanup to clean up entries older than 30 days
- Log files: job SCS_CleanupFiles to clean up files older than 30 days
The execution is logged at file level.
Objects list
In the scs_AdminDB the following objects are configured:
| Name | Type | Original name | Description |
|---|---|---|---|
| T_CommandLog | table | CommandLog | Logging table |
| sp_CommandExecute | procedure | CommandExecute | SP used for command executing |
| sp_DatabaseIntegrityCheck | procedure | DatabaseIntegrityCheck | Check DB |
| sp_IndexOptimize | procedure | IndexOptimize | Reindexing and update statistics |
Job list and frequency
The maintenance will be implemented through SQL Agent scheduled jobs.
| Maintenance | DB's | Job | Scheduling |
|---|---|---|---|
| Integrity Checks | System DB's | SCS_SysDbIntegrity | Weekly, Sunday 00:00 am |
| Integrity Checks | User DB's | SCS_UserDbIntegrity | Weekly, Sunday 00:15 am |
| Index and update statistics modified only | User DB's | SCS_AllUserDbIndexing | Weekly, Sunday 01:00 am |
| Update statistics all | User DB's | SCS_AllUserDbUpdateStats | Weekly, Wednesday 01:00 am |
| Clean up command log table | n/a | SCS_CommandLogCleanup | Daily 04:00 am |