SLM Database Maintenance Guide
SLM database maintenance helps administrators maintain the performance, reliability and recoverability of the Microsoft SQL Server databases supporting Safety Lifecycle Manager. SQL Server maintenance plans can automate recurring administrative tasks and provide a structured approach to routine database maintenance.
The SLM training demonstrates two maintenance schedules: a daily plan covering backups, database integrity checks and cleanup tasks, and a weekly plan covering index and statistics maintenance. These tasks are configured through SQL Server Management Studio and scheduled for periods when database activity is low.
This guide explains the SLM database maintenance workflow, including daily and weekly maintenance plans, database backups, integrity checks, cleanup, index maintenance and monitoring maintenance-plan history.
Planning SLM Database Maintenance
SLM database maintenance uses Microsoft SQL Server maintenance plans to organize recurring database administration tasks. A maintenance plan provides a scheduled workflow for operations intended to maintain the health and efficiency of the database environment.
The training identifies several important maintenance activities:
- Regular database backups
- Index maintenance
- Database integrity checks
- Updating statistics
- Database and backup cleanup
These tasks address different aspects of database administration. Backups provide recoverable copies of data, integrity checks help identify database consistency problems, index and statistics maintenance support query processing, and cleanup tasks remove older files or history according to the organization’s retention requirements.
Administrators responsible for the underlying SQL Server environment should also review the SLM Database Configuration guide alongside these maintenance procedures.
Creating SQL Server Maintenance Plans
The SLM database maintenance workflow demonstrated in the training uses SQL Server Management Studio and the Maintenance Plan Wizard.
Within SQL Server Management Studio, expand the Management folder and locate Maintenance Plans. The Maintenance Plan Wizard can then be opened from the context menu.
If the wizard cannot run because SQL Server Agent is stopped, the training demonstrates starting SQL Server Agent before returning to the Maintenance Plan Wizard.
The example creates two separate plans:
- Daily Maintenance Plan
- Weekly Maintenance Plan
Separating the plans allows different database tasks to run on different schedules. The appropriate execution time should be selected according to the organization’s operating requirements and should ideally avoid periods of normal user activity.
Daily Backup and Integrity Tasks
The daily SLM database maintenance plan demonstrated in the training contains four principal tasks:
- Backup Database – Full
- Check Database Integrity
- Cleanup History
- Maintenance Cleanup Task
The order of the tasks can be adjusted within the Maintenance Plan Wizard. In the demonstrated plan, the full database backup is moved to the beginning, followed by the integrity check and cleanup operations.
The daily schedule should be configured for a suitable low-activity period. The training uses midnight as an example, while noting that the actual time should be determined by the organization.
The purpose is not to prescribe one universal maintenance time but to ensure that recurring database administration is scheduled appropriately for the SLM environment.
Configuring SLM Database Backups
For the demonstrated full-backup task, the maintenance plan is configured to include all user databases and to ignore databases whose state is not online.
The backup can be directed to disk or, where applicable, URL-based storage. The training example uses disk and the default SQL Server backup directory.
The demonstrated backup configuration also enables:
- Backup compression
- Backup checksum
- Continue on error
Where an environment contains a large number of databases, backup duration should be monitored. The training explains that administrators can divide databases between separate maintenance plans if processing all databases in one plan results in an unsuitable runtime.
For example, an environment with 20 databases could divide them between two maintenance plans rather than processing all 20 through a single schedule.
Integrity Checks and Database Cleanup
Database integrity checks form another part of the daily SLM database maintenance plan. The demonstrated configuration selects all user databases and ignores databases that are not online.
The integrity task uses the default options shown in the training.
The daily plan also contains cleanup operations. Cleanup History is configured with a retention period, with four weeks used as the demonstrated default. The appropriate period can be changed according to company policy and can be expressed in hours, days, weeks, months or years.
The Maintenance Cleanup Task removes old backup files after the configured period. In the training example, the task points to the SQL Server backup folder and targets files using the .bak extension.
Backup retention should therefore be configured according to the organization’s own backup, recovery and retention requirements rather than assuming that the example period is appropriate for every SLM installation.
Monitoring Maintenance Plan History
After the daily plan has been created, SQL Server Management Studio displays it within Maintenance Plans. Administrators can open the plan to review its individual steps and modify task settings where required.
Any modifications made to the maintenance plan should be saved before closing the configuration.
The maintenance-plan history provides useful operational information about previous executions. The training highlights information including:
- Execution date
- Plan and subplan information
- Duration
Duration is particularly useful when reviewing SLM database maintenance because it shows how long the scheduled work takes to complete.
If a plan containing all databases takes too long, administrators can consider splitting the database workload between multiple maintenance plans or adjusting schedules to prevent maintenance processes from overlapping.
Weekly Index and Statistics Maintenance
The second SLM database maintenance plan demonstrated in the training runs weekly and focuses on index and statistics maintenance.
As with the daily plan, it should be scheduled for a period when users are not actively working with the database. The example uses Saturday at 1:30 AM so that it does not run at the same time as the daily midnight plan.
The training demonstrates the following maintenance operations:
- Rebuild Index
- Reorganize Index
- Update Statistics
The appropriate index-maintenance strategy depends on the SQL Server environment and edition being administered. The training discusses different choices for Standard and Enterprise environments and demonstrates the available tasks within the Maintenance Plan Wizard.
Because SQL Server capabilities and online index-operation support can depend on SQL Server version, edition and index characteristics, administrators should confirm the appropriate approach for their deployed SQL Server environment rather than applying one maintenance configuration universally.
Microsoft provides current guidance on reorganizing and rebuilding SQL Server indexes, including considerations for fragmentation, statistics and resource usage.
Configuring Index Maintenance Tasks
The demonstrated weekly maintenance plan applies its index operations to the relevant user databases while excluding databases that are not online.
For the Rebuild Index task, the training also demonstrates an advanced option intended to keep the index online during the operation where the SQL Server environment supports that capability.
The Reorganize Index and Update Statistics tasks are then configured for the selected databases using the options demonstrated within the wizard.
After the required operations have been selected and configured, SQL Server Management Studio can write a maintenance-plan report to a text file. The wizard then provides a summary of the plan before it is created.
Once completed, refreshing the Maintenance Plans area shows both the daily and weekly plans available for administration.
Building a Reliable Database Maintenance Routine
SLM database maintenance should be treated as a recurring operational process rather than a one-time configuration task.
A practical maintenance routine should consider:
- When backups and other maintenance tasks run
- Whether maintenance overlaps with periods of normal SLM usage
- How long individual plans take to complete
- Which databases are included in each plan
- Backup and maintenance-history retention requirements
- The SQL Server version and edition in use
- Whether daily and weekly tasks overlap with one another
The maintenance-plan history can then be reviewed to determine whether schedules remain appropriate as database size and workload change.
Database maintenance is also only one layer of SLM administration. The SLM Application Maintenance guide covers IIS and PHP application logging, while the SLM Background Task Manager guide covers application background processing.
Together with appropriate SQL Server configuration, these maintenance practices help administrators establish a structured approach to supporting the SLM application and its underlying database environment.
