Database growth rate (detailed)
Job details
| Name: |
Database growth rate (detailed) |
| 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: |
10 5 1,4 * |
| Requires engine install: |
Yes |
| Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & (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 and track the growth rate of databases by collecting size data for all database files, including transaction log files.
- Why: This job is critical for understanding database capacity and planning for future growth. By tracking the growth of database files, administrators can manage resources more effectively and prevent potential issues related to database capacity.
- Manual checking: You can check this manually in the database by issuing the following SQL commands:
SELECT db_name, db_file_size, tr_log_size, histr_date
FROM dbw_db_size_histr_detailed
ORDER BY db_name, histr_date;
Details
- Category: Capacity
- Job Name: Database growth rate (detailed)
- Description: Collects size of all database files (including transaction log files) to visualize the growth rate for all databases.
- Version: 1.2
- Company: dbwatch.no
- Group: com.dbwatch.job
- Artifact ID: ms_database_growth_rate_detailed_2000
- Implementation: The job involves creating necessary tables and procedures to capture and analyze database and transaction log sizes over time, ensuring data is collected at specified intervals and kept updated.
Dependencies
- This task relies on several SQL Server system tables and previously established tables for tracking sizes:
- master.dbo.sysaltfiles
Execution Logic
- The stored procedure injected by the job collects the current sizes of the databases and transaction logs and compares these with the last history records to decide whether to update the historical data.
- Uses cursor operations for iterating through database details stored in system tables.
- Rankings are generated for databases based on size to identify the largest databases.
Reporting Template
- Version: 2
- Title: Database growth rate (detailed)
- Scheduled: Hourly
- Presentations in the report:
- Chart showing growth rate for the top 5 largest databases.
- Table detailing size information for the top 20 largest databases.
- Chart comparing data file and transaction log sizes for the top 10 databases.
Scheduled Execution
- Default Schedule: Every hour at 5 minutes past the hour
Note: This detailed monitoring is configured for instances running Microsoft SQL Server 2000 and Azure SQL Managed Instance by specifying the database types and conditions in the component query.