Database growth rate (detailed)
Job details
| Name: | Database growth rate (detailed) | |
| Platform: | Sqlserver | |
| Category: | Capacity | |
| Description: | Collects size of database files (including transaction log files) to visualize the growth rate for the largest databases. | |
| Long description: | Collects size of database files (including transaction log files) to visualize the growth rate for the largest databases. | |
| Version: | 1.8 | |
| Default schedule: | 10 5 1,4 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| min histr interval | 360 | The minimum number of minutes before a new history record will be collected in the history table. |
| collect statistics for databases | List of databases (separated by comma) for which statistics will be collected. You can use % (percent sign) to represent wild card characters. If no value specified, statistic for the largest databases will be collected (number of databases is controlled by the parameter “max databases” with the default value 10). | |
| max databases | 5 | Number of the largest databases for which statistics will be collected (default value 5). |
| max histr interval | 730 | The maximum number of days before old history records will be removed from the history table (default 2 years). |
Job Summary
- Purpose: The purpose of this job is to monitor and visualize the growth rate of database files, including transaction log files, for the largest databases or those specified by the user.
- Why: This job is important to ensure that the database storage capacity is managed proactively. Monitoring growth rates helps in anticipating storage needs and optimizing database performance. Excessive growth can lead to performance degradation and increased costs.
- Manual Checking: To manually check the database growth, SQL queries targeting system views or tables that store file size data for databases can be used. For example, querying ‘sys.master_files’ or ‘sys.database_files’ in SQL Server can provide immediate insights into the current database file sizes.
Detailed Description
- This monitoring task collects statistics on the size of database files and transaction logs, specifically targeting instances where the database major version is greater than 2000 and it has a SQL Server engine or an Azure SQL Managed Instance.
- It includes a range of parameters to adjust how data is gathered, such as historical data collection intervals and targeting specific databases for monitoring.
Implementation Details
- The SQL provided within the job definition includes procedures that:
- Create necessary tables to store detailed size information.
- Execute cursor-based SQL to gather database sizes, update rank tables, and manage historical data.
- Conditions are evaluated to decide whether new history records need to be inserted based on the minimum historical interval and the size rank of databases.
- Cleanup actions involve truncating tables and deleting outdated records based on parameterized intervals.
Dependency Tables and Cleanup Actions
- The task depends on system tables such as ‘master.dbo.sysaltfiles’ and ‘master.dbo.sysdatabases’ for gathering data.
- It also depends on self-managed tables like ‘dbw_db_size_detailed’ and ‘dbw_db_size_histr_detailed’ for storing historical data.
- Cleanup actions are set within the dependencies, specifying whether failed operations should trigger a cleanup.
Parameters and Upgrades
- Parameters such as ‘min histr interval’, ‘max databases’, and ‘max histr interval’ are used to fine-tune the operation of this job.
- Upgrades from previous versions are managed through scripts that check for the existence of parameters or columns and add them if missing.
Reporting Templates
- The job uses a report template to visualize data in charts and tables, providing insights into:
- Data and Transaction-log size for the largest databases.
- Historical growth rates and size information for databases.
- The template configures visibility, formatting, and the maximum number of database entries to display.
Conclusion
- This dbWatch job serves a critical role in capacity planning and operational maintenance of databases by continuously tracking their growth. Proper configuration and periodic review of the output help maintain optimal database performance and capacity management.