Auto growth event collector

Job details

Name:	Auto growth event collector
Platform:	Sqlserver
Category:	Capacity
Description:	Collects statistics on how often an auto-growth event has occurred.
Long description:	By knowing which databases are performing auto-growth events allows you to adjust those database file growth properties so they will perform auto-growth events less frequently.
Version:	1.11
Default schedule:	12 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Name	Default value	Description
history threshold	60	The number of days before the history record will be removed from the history table.
max objects per graph	5	The maximum number of objects visualized in the report graphs.

Purpose: The purpose of this job is to collect and analyze auto-growth events in SQL Server databases to manage and optimize file growth settings and reduce potential performance impacts during these events.
Why: This job is essential because unmanaged auto-growth settings can lead to frequent auto-growth events which in turn hold up database processing, causing slower response times for SQL commands during these events. By monitoring these, adjustments can be made to improve overall database performance and efficiency.
Manual checking: You can manually check auto-growth events in a database by using SQL Server functions and system tables.

SELECT event_name, database_id, COUNT(*) as event_count FROM fn_trace_gettable('trace_file', DEFAULT) GROUP BY event_name, database_id;

The auto growth event collector job tracks and stores the occurrences of database file auto-growths, gathering data on how often each event happens, along with the size of growth and the specific database affected.
Capturing this data allows database administrators to adjust settings proactively, helping ensure that databases do not frequently auto-grow.
The long description in the content expresses concerns on how auto-growth interrupts database operations and the negative impact it has on performance, reinforcing the need for this monitoring task.

This task depends on essential system tables in SQL Server:
Dependency on: master.dbo.sysaltfiles, master.dbo.sysdatabases for database file and configuration information.

The implementation section gives detailed SQL scripts for creating necessary tables and stored procedures for handling the auto-growth data:
Tables to store event data and logs about when and how data files and logs are growing.

There are detailed reporting templates included within the job, which show:
An overview of all auto-growth events, providing in-depth analyses like event name, database name, first and last occurrence, total events, and total growth in MB.

Automated procedures to fetch the latest auto-growth incidents, update historical records, and prepare reporting data.
SQL code is included to handle both installation and upgrades of the job scripts, ensuring new parameters or tweaks are appropriately managed in existing setups.

Maintaining this monitoring ensures that DBAs can be proactive about database file management, potentially reducing unwanted pauses in database operations due to auto-sizing. This leads to better performance and less reactive maintenance needed in response to unexpected growth events.