Filegroups growth rate

Job details

Name:	Filegroups growth rate
Platform:	Sqlserver
Category:	Capacity
Description:	This procedure collects space usage for the largest 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 on a SQL Server instance.
Long description:	This procedure collects space usage for the largest 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 on a SQL Server instance.
Version:	2.0
Default schedule:	35 6 5 *
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.
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 5).
max filegroups	20	Number of the largest filegroups for which statistics will be collected (default value 20). To keep historical data for all filegroups set the parameter value to -1. Keep in mind that the local history table dbw_filegroups_growth_histr (in dbWatch database) can reach large sizes if there are several hundred of databases per SQL Server.
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 collect data on the growth rates of filegroups within SQL Server databases. It aims to track space utilization efficiently over time for the largest filegroups in each database.

Why

This job is important to ensure databases are not unexpectedly running out of space. By monitoring the growth and space usage, it can help prevent potential data storage issues and optimize database performance. Moreover, it supports administrative planning for storage capacity.

Manual checking

You can check this manually in the database by issuing the following SQL commands:

SELECT DB_NAME() as DatabaseName, groupname as FileGroupName, size*8 as Size, fileproperty(name,'SpaceUsed')*8 as SpaceUsed
FROM sysfiles t1
JOIN sysfilegroups t2 ON t1.groupid = t2.groupid

Technical Details

Tables Created

Table Name	Description
dbw_filegroups_growth	Holds current growth data for each filegroup
dbw_filegroups_growth_histr	Stores historical growth data for reporting and tracking purposes

Stored Procedure and Its Role

proc_filegroups_growth: It collects the growth data of filegroups across databases using an undocumented stored procedure ‘sp_MSforeachdb’ to execute statements against `dbo.sysfiles`.

Job Scheduling

The default schedule for running this job is defined under the tag ‘default-schedule’. The format suggests a monthly execution, specifically at 06:35 AM on the 5th day of each month.

Report Generation

Reports generated by this task list database and filegroup names, used and free space, and configuration settings. Growth visualization over time and specific filegroup growth rates are intended for detailed analytical purposes.

Parameters and Cleanup

A series of parameters like ‘min histr interval’, ‘max filegroups’, and ‘max histr interval’ govern the maintenance and data capturing frequency. For each case of failure, associated cleanup procedures ensure no residual data disrupts the system’s integrity.

Upgrade Procedures

The implementation includes upgrade codes ensuring that table structures and procedures remain aligned with the latest configurations without database integrity compromise.

Dependencies

The job has explicit dependencies on its own procedures for redundancy and checks against the ‘dbw_filegroups_growth’ and ‘dbw_filegroups_growth_histr’ tables to ensure that all required structures exist before execution.

This comprehensive tracking and reporting framework provided by the dbWatch task is essential for database managers overseeing large SQL Server environments, ensuring they have the tools needed to preempt storage issues effectively.