DBA Scripts: Get Autogrowth History

July 22, 2026

–

Part of the DBA-Tools Project.

SQL Server autogrowth is one of those settings that is often ignored until it becomes a production problem.

A database file growing during normal business hours is usually a sign that SQL Server is reacting to a capacity issue. The engine pauses normal operations, extends the data or log file, then continues processing.

Depending on the file size, storage performance, and workload, this can take milliseconds or create noticeable application latency.

In production environments, autogrowth should be treated as a safety mechanism, not a capacity management strategy.

Regularly reviewing autogrowth history helps identify:

Undersized database files.
Incorrect growth settings.
Unexpected workload increases.
Storage capacity issues.
Transaction log sizing problems.

This script reads autogrowth events from the SQL Server default trace and shows when files grew, how much space was added, and how long the operation took.

Why Autogrowth History Matters

Autogrowth problems are often difficult to spot.

The database remains online. Monitoring dashboards remain green. No alerts are triggered.

However, users may experience:

Slow queries.
Increased application response times.
Longer ETL execution.
Slower transaction processing.

When autogrowth occurs:

SQL Server extends the data or log file.
Active workload can be impacted.
Storage latency may increase.
Transaction processing may slow.
Frequent growth events create unnecessary overhead.

Autogrowth history provides evidence to correctly size database files instead of guessing.

Common Production Examples

Small Growth Increment

A database configured with:

FILEGROWTH = 10MB

may grow hundreds of times per day.

The database keeps working, but SQL Server repeatedly pauses to extend files.

Transaction Log Growth During Peak Hours

Transaction logs growing during busy periods can increase:

Commit latency.
Log backup duration.
Availability Group synchronisation delays.
Recovery time.

ETL or Batch Processing Growth

Large data loads can expose incorrectly sized files when:

Data files were created too small.
Growth settings were inherited from defaults.
Capacity planning did not include workload increases.

Common Symptoms

Autogrowth issues may appear as:

Intermittent application slowdowns.
Slower transaction commits.
ETL jobs running longer than expected.
Unexpected database file size increases.
Increased transaction log backup activity.
Repeated growth messages in SQL Server error logs.

When to Run This Script

Use this script during:

Regular SQL Server health checks.
Capacity planning reviews.
Performance troubleshooting.
Storage reviews.
Migration validation.
New application deployments.
Investigations into unexpected file growth.

A healthy production environment should rarely rely on frequent autogrowth events during normal operation.

The Script

Run this against your SQL Server instance.

/*
Script Name : Get-AutogrowthHistory
Category    : monitoring
Purpose     : Reads autogrowth events from the SQL Server default trace.
              Identifies undersized files and inefficient growth settings.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-autogrowth-history/)
Requires    : VIEW SERVER STATE
Notes       : Default trace history is limited and rolls over.
              Available history depends on server activity.

              EventClass 92 = Data File Autogrow
              EventClass 93 = Log File Autogrow

              Recommended fix:
              Pre-size files and configure fixed MB FILEGROWTH values.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

DECLARE @tracepath NVARCHAR(256);

SELECT @tracepath = path
FROM sys.traces
WHERE is_default = 1;

IF @tracepath IS NULL
BEGIN
    SELECT 'Default trace is not running or has been disabled.' AS note;
    RETURN;
END;

SELECT
    DB_NAME(e.DatabaseID) AS database_name,
    e.FileName AS data_file,
    CASE e.EventClass
        WHEN 92 THEN 'Data File Autogrow'
        WHEN 93 THEN 'Log File Autogrow'
    END AS event_type,
    e.StartTime AS grew_at,
    CAST(e.IntegerData * 8.0 / 1024 AS DECIMAL(10,2)) AS growth_mb,
    CAST(e.Duration / 1000.0 AS DECIMAL(10,1)) AS duration_ms,
    DATENAME(WEEKDAY, e.StartTime) AS day_of_week,
    DATEPART(HOUR, e.StartTime) AS hour_of_day
FROM sys.fn_trace_gettable(@tracepath, DEFAULT) AS e
WHERE e.EventClass IN (92, 93)
AND e.DatabaseID > 4
ORDER BY e.StartTime DESC;

Running from the Repository

Clone the repository:

git clone https://github.com/peterwhyte-lgtm/dba-tools

Navigate into the repository:

cd dba-tools

Initialize the environment:

.\Initialize-Environment.ps1

Run the script:

.\run.ps1 Get-AutogrowthHistory

To run against another SQL Server instance:

.\run.ps1 Get-AutogrowthHistory -ServerInstance SQLSERVER01

Repository contents:

SQL Script
PowerShell execution wrapper

Example Output

Screenshot: dba-scripts-get-autogrowth-history-output.png

Example:

database_name	data_file	event_type	grew_at	growth_mb	duration_ms	day_of_week	hour_of_day
migdb_B8A15B78	migdb_B8A15B78_data	Data File Autogrow	16/07/2026 19:59:08	10.00	0.0	Thursday	19
migdb_B8A15B78	migdb_B8A15B78_data	Data File Autogrow	16/07/2026 19:59:08	10.00	4.0	Thursday	19
migdb_B8A15B78	migdb_B8A15B78_log	Log File Autogrow	16/07/2026 19:59:07	10.00	480.0	Thursday	19

Interpreting the Results

Growth Events During Business Hours

Frequent growth during normal workload periods usually indicates:

Files were created too small.
Growth increments are too low.
Database usage has increased.
Capacity planning needs updating.

Growth Size

The growth_mb column shows how much space was added.

Example:

500 growth events × 10MB = 5GB expansion

This indicates SQL Server repeatedly extending the file instead of allocating enough space upfront.

A planned 5GB growth operation is usually preferable to hundreds of smaller expansions.

Growth Duration

The duration_ms column shows how long the growth operation took.

Long durations may indicate:

Slow storage.
Large growth operations.
Storage contention.
Transaction log expansion.

Transaction log growths are especially important because they cannot benefit from Instant File Initialization.

Best Practices

Pre-size Database Files

Create files at a size that supports expected workload growth.

Avoid relying on repeated autogrowth events.

Use Fixed MB Growth

Prefer:

ALTER DATABASE YourDatabase
MODIFY FILE
(
    NAME = YourDatabase_Data,
    FILEGROWTH = 1024MB
);

over percentage growth.

Fixed MB values provide predictable behaviour.

Avoid Default Growth Settings

Default SQL Server settings are rarely suitable for production workloads.

Common problematic settings:

1MB
10MB
10%

Review Growth Trends Regularly

Autogrowth history should be reviewed alongside:

Database file sizes.
Free disk space.
Backup trends.
Workload changes.

Related Scripts

Frequently Asked Questions

How do I check SQL Server autogrowth history?

This script reads autogrowth events captured by the SQL Server default trace.

Are autogrowth events bad?

No.

Autogrowth is an important safety feature.

The problem is frequent unexpected growth during normal operation.

Should autogrowth be disabled?

No.

Autogrowth should remain enabled as protection against running out of space.

The goal is to configure it correctly, not remove it.

Should SQL Server autogrowth use percentages?

Usually no.

Fixed MB growth values provide more predictable behaviour, especially for large databases.

Summary

Autogrowth history is a simple but valuable SQL Server health check.

Regularly reviewing growth events helps identify undersized files, incorrect growth settings, and capacity issues before they affect production workloads.

A well-managed SQL Server environment should rarely depend on autogrowth.

Size files correctly, configure sensible growth increments, and use autogrowth as a safety net rather than a normal operating process.

Blog Categories

Comments

Leave a Reply Cancel reply