DBA Scripts: Get Autogrowth History

---

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

Every autogrowth event is the engine telling you a file was sized too small, and the history of those events tells you exactly where, when, and what it cost.

Autogrowth problems hide well because nothing fails: the database stays online, the dashboards stay green, and no alert fires.

What users feel instead is the pause. When a file grows, the sessions that need the new space wait for the extension to finish, so the symptom surfaces as intermittently slow queries, commits that take longer than the workload justifies, and ETL windows that stretch for no visible reason. The history this script reads from the default trace turns those vague symptoms into dated, sized, timed events you can put next to your slow-query reports.

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.

---

When to Run This Script

Run it as part of a regular health check or capacity review, before and after a migration, and any time a performance investigation or an unexpected jump in file size makes you ask what has been growing. The pattern to expect from a healthy production environment is that growth events are rare and deliberate, because files sized ahead of demand hardly ever need to grow during normal operation; a busy history in this output is itself the finding.

---

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;

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

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

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Read autogrowth history from the default trace:
.\run.ps1 Get-AutogrowthHistory

# To run against a remote sql server:
.\run.ps1 Get-AutogrowthHistory -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/disk-space/Get-AutogrowthHistory.sql
• powershell/wrappers/monitoring/disk-space/Get-AutogrowthHistory.ps1

---

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 is where this output teaches something you cannot see anywhere else, and two rows above from 16 July make the point better than a benchmark could. The same database grew its data file by 10MB in 4 milliseconds and its log file by the same 10MB in 480 milliseconds, one second apart on the same disk. That 120x gap is Instant File Initialization at work: with IFI enabled, a data file claims new space without zeroing it first, while a transaction log always writes zeroes over every new byte, because crash recovery depends on knowing where the log genuinely ends. Long data-file durations therefore point at slow or contended storage, or at IFI not being enabled at all; long log durations are the log doing what it must, which is why log files deserve generous pre-sizing and fixed-MB growth more than any other file. If your data-file growths also run hundreds of milliseconds, check IFI before blaming the SAN.

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

• Storage and Capacity (hub)
• Database Files Detail
• Database Sizes and Free Space
• Transaction Log Size and Usage
• VLF Count
• Database Free Space Summary
• Filegroup Space
• Disk Space
• Database Growth Risk and Forecast
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

---

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.