Filegroups growth rate

Job details

Name:	Filegroups growth rate
Platform:	Sqlserver
Category:	Capacity
Description:	This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance.
Long description:	This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance.
Version:	2
Default schedule:	0 * * *
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 record the growth rates and space usage of filegroups across different databases on a SQL Server or Azure SQL Managed Instance. This job involves collecting current space usage and historical growth data, which helps in managing database capacity effectively.
Why: Monitoring filegroup size and growth is crucial for database administrators to ensure that databases operate efficiently and have enough available space. This job helps in identifying potential space shortages or abnormal growth trends, allowing preemptive action to be taken to manage storage resources.
Manual checking: You can check this manually in the database by issuing the concluding SQL command from the job script:

select dbname "Database", round(convert(float,sum(size))/1024.,2) "Total size", round(convert(float,sum(spaceused))/1024.,2) "1 Space used", round(convert(float,sum(size)-sum(spaceused))/1024.,2) "2 Free space" from dbw_filegroups_growth group by dbname order by 2 desc

Job Configuration and Implementation

Implementation: The job includes SQL procedure implementations for creating necessary tables (dbw_filegroups_growth, dbw_filegroups_growth_histr) and a stored procedure (proc_filegroups_growth) to fetch and update the filegroup size, space used, and other relevant metrics across all databases.
Script Execution: The monitoring scripts run automatically based on a default schedule configured within the job template. They use SQL Server’s “sp_MSforeachdb” to apply operations across all databases.
Dependencies: The job relies on self-referencing dependencies to ensure that growth data is consistent and compiled comprehensively for reporting purposes. These dependencies also involve clean-up operations in case of failures during the job execution.

Reporting and Visualization

Data Presentation:
- Tables and charts are used to present data on the filegroup sizes, space used, and growth rates.
- Different presentations are configured for showing database space statistics, historical growth data, and data file configurations, essential for comprehensive capacity planning.

Job Dependencies and Triggers

Dependencies: The job depends on the successful execution of related tasks, such as ‘proc_filegroups_growth’, and relies on the data collected in ‘dbw_filegroups_growth’ and ‘dbw_filegroups_growth_histr’ tables set in previous runs.
Cleanup: The job includes mechanisms to clean up after itself in event of a failure in order to maintain system stability and data integrity.

Summary

This dbWatch job is an essential component for maintaining operational efficiency and stability in SQL Server environments by monitoring database filegroups’ space usage and growth trends. It helps ensure that adequate database resources are available and provides critical data that informs capacity planning and management decisions.