Database growth rate (aggregated)

Job details

Name:	Database growth rate (aggregated)
Platform:	Sqlserver
Category:	Capacity
Description:	Collects size of all database files (including transaction log files) to visualize the growth rate for all databases.
Long description:
Version:	1.2
Default schedule:	15 4 5 *
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.

Job Summary

Purpose: The purpose of this job is to monitor the growth rates of databases on SQL Server instances (including the versions compatible with SQL Server 2000), focusing on both data files and transaction log files.
Why: This job is crucial for understanding how databases are growing over time, which helps in capacity planning and optimizing resource allocation. Monitoring database growth can alert administrators to unexpected increases that might require intervention to prevent performance degradation or space issues.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT dbid, name, filename, ROUND(size*8/1024.0, 1) AS size FROM master.dbo.sysaltfiles ORDER BY dbid;

Job Configuration Details

“Database growth rate (aggregated)” is the main task handled by this job.
It collects the size and count of both data files and transaction log files across all databases in the instance to visualize and track growth trends.
The task selectively targets SQL Server 2000 or instances resembling its configuration, reflecting its broader compatibility by including Azure SQL Managed Instances.

Dependencies and Execution

This job depends on itself and several other components within the database environment:
“dbw_db_size_histr_aggr” table for storing history of database sizes.

Reporting and Visualization

The task allows generating reports to visualize the growth of databases:
Displays data for data file sizes, transaction log sizes, and their combined total in both table and chart formats.
Database Growth Rate (Aggregated):

Scheduled Operations

Default scheduling suggests a monthly assessment (executed at 4:15 AM every 5th day of the month) which helps in maintaining an up-to-date assessment of the database growth trends without consuming excessive resources.

Upgrade and Maintenance

The procedure includes an upgrade path for minor version changes, reflecting adjustments to its internal SQL to maintain or improve performance and accuracy.
Optimized for straightforward installation in a production environment with configurations to force installation when required, ensuring the latest version is always deployed without manual intervention.

Implementation Code

SQL code provided manages the creation and updating of database size history, including details like the total size of databases and when the last record was collected.
It effectively utilizes parameters such as the minimum history interval to optimize database performance and reduce unnecessary data collection.

This job configuration effectively helps database administrators monitor and manage the growth of databases on SQL Server platforms, particularly on legacy systems akin to SQL Server 2000, as well as on modern managed solutions like Azure SQL Managed Instances.