Automated Maintenance Package

The Automated Maintenance Package is an permium package for dbWatch Control Center, designed to streamline routine database maintenance tasks. By automating essential upkeep, this package helps ensure optimal database performance and stability with minimal manual intervention.

Package Overview

The Automated Maintenance Package includes a suite of maintenance jobs tailored to simplify database management by automating repetitive and resource-intensive tasks. This package is ideal for database administrators seeking to improve operational efficiency and reduce the likelihood of performance degradation over time.

Benefits

• Efficiency: Automate routine maintenance tasks, freeing up DBA resources for strategic initiatives.
• Performance Optimization: Regular upkeep ensures databases operate at peak efficiency.
• Consistency: Standardize maintenance procedures across all SQL Server instances.
• Risk Mitigation: Proactive maintenance reduces the likelihood of unexpected issues.

Jobs Included

Below is an overview of the jobs included in the Automated Maintenance Package. For more details on each job, refer to the Maintenance Jobs documentation.

• Backup All databases: Takes backup of all application and system databases.
• Backup All transaction logs: Takes backup of all transaction logs for databases running in FULL recovery mode.*
• Check database and server principal mapping: Checks if the database owner (dbo) is mapped into any Server Login (server principal).
• Cleanup MSDB history tables: This task deletes entries in the MSDB database history tables which holds statistics of backup/restore, jobs and maintenance plan executions.
• Cycle error log: This task cycle MS SQL Server error log and Agent error log files.
• DBCC CHECKDB: Checks the logical and physical integrity of all the objects in all databases by performing the DBCC CHECKDB operation.
• DBCC UPDATEUSAGE: This task is performing the DBCC UPDATEUSAGE operation to corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
• External fragmentation: External (logical) fragmentation occurs when an index leaf page is not in logical order. It occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server to perform extra work to return ordered results.
• Internal fragmentation check: Checks the internal fragmentation for tables and indexes in all databases. The information is extracted from the dynamic management function (view) sys.dm_db_index_physical_stats.
• Page verify option: This job checks whether PAGE_VERIFY database option is set to CHECKSUM. This helps provide a high level of data-file integrity.
• SQL Server performance counters: Checks if SQL Server performance counters are missing.
• Rebuild indexes: Rebuilds fragmented indexes in all databases.
• Rebuild indexes in table: Rebuilds fragmented indexes in all tables listed in the ‘table list’ parameter.
• Reorganize indexes: Reorganizes fragmented indexes in all databases.
• Reorganize indexes in table: Reorganizing fragmented indexes in all tables listed in the ‘table list’ parameter.
• Shrink transaction logs: This procedure shrinks transaction log files which are detected by ‘Transaction log size check’ alert which checks the size of the transaction log (log file(s) size), and compares it to the database size (data file(s) size).
• Suspect pages: Monitors suspect pages statistics in suspect_pages table.
• Update index statistics: Update statistics in all (non-system) databases.
• Update statistics: Update statistics in all (non system) databases.

Configuration Options

Each job in the Automated Maintenance Package can be customized to meet your specific operational needs. Configurable options include:

• Frequency: Set the job execution frequency (daily, weekly, monthly).
• Resource Thresholds: Define thresholds to control resource usage during maintenance jobs.
• Notification Settings: Configure alert options for job failures or other significant events.

💡 Use Case

Scenario:
A mid-sized enterprise with multiple SQL Server instances faced performance degradation due to fragmented indexes and outdated statistics. By implementing the Automated Maintenance Package, they achieved:

• 25% improvement in query response times
• Consistent database performance across all instances
• Reduced manual intervention, allowing DBAs to focus on strategic tasks

📄 Licensing Information

The Automated Maintenance Package is a premium offering.
To obtain a license or request a quote, please visit our Pricing & Licensing page.