Capturing Autogrowth Events With database_file_size_change

The database_file_size_change event tells you when a file grew, by how much, and whether SQL Server did it on its own. That last flag settles most arguments about who grew the file.

Why a bigger file needs a witness

It is Monday morning. The data drive is much fuller than it was on Friday. The application team says the database “just grew.” The maintenance team says nobody touched it. You are the person in the middle.

Without a record, you guess. With this event, you read the answer. A file can grow because a query filled it and SQL Server expanded it automatically. It can also grow because a person ran ALTER DATABASE. Those are two very different conversations.

The event carries a flag called is_automatic. Zero means someone resized the file. One means autogrowth. Let me prove it on a small demo database, then you can try it on your own server.

Meet the fields and start the session

First, create the database and list the data fields the event can give you. You get the size change, the new total size, the file name and type, a duration and the automatic flag.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
SELECT name, type_name
FROM sys.dm_xe_object_columns
WHERE object_name = N'database_file_size_change'
  AND column_type = N'data'
ORDER BY column_id;

Now the event session. This demo creates a server-level object, GrowthDemo, and removes it at the end. It watches only our demo database and keeps events in a ring buffer in memory.

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'GrowthDemo')
    DROP EVENT SESSION GrowthDemo ON SERVER;
GO
DECLARE @sql nvarchar(max) = N'
CREATE EVENT SESSION GrowthDemo ON SERVER
ADD EVENT sqlserver.database_file_size_change
    (WHERE database_id = ' + CONVERT(nvarchar(10), DB_ID(N'SqlAuthorityDemo')) + N')
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);';
EXEC (@sql);
GO
ALTER EVENT SESSION GrowthDemo ON SERVER STATE = START;

Resize by hand and read the event

A new database on my server starts with an 8 MB data file. I resize it to 16 MB myself. Then I read the ring buffer after a two second wait, because events arrive in a short batch.

ALTER DATABASE SqlAuthorityDemo MODIFY FILE (NAME = SqlAuthorityDemo, SIZE = 16MB);
WAITFOR DELAY '00:00:02';
GO
WITH TargetData AS (
    SELECT CONVERT(xml, t.target_data) AS x
    FROM sys.dm_xe_session_targets AS t
    JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
    WHERE s.name = N'GrowthDemo' AND t.target_name = N'ring_buffer'
)
SELECT e.n.value('(@name)[1]', 'sysname') AS EventName,
       e.n.value('(data[@name="size_change_kb"]/value)[1]', 'bigint') AS SizeChangeKb,
       e.n.value('(data[@name="is_automatic"]/value)[1]', 'bit') AS IsAutomatic
FROM TargetData
CROSS APPLY x.nodes('/RingBufferTarget/event') AS e(n)
ORDER BY e.n.value('(@timestamp)[1]', 'datetime2');

One event comes back. SizeChangeKb is 8192, which is the 8 MB I added, and IsAutomatic is 0. A person did this, and the event says so.

Now cause a real autogrowth

Now let SQL Server do it. I set the data file to grow in 8 MB steps and load 3,000 rows of 8,000 bytes each. That is more than the 16 MB file can hold. Notice that the same buffer still holds our manual resize, so you can compare both kinds side by side.

ALTER DATABASE SqlAuthorityDemo MODIFY FILE (NAME = SqlAuthorityDemo, FILEGROWTH = 8MB);
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.GrowthDemo (Id int IDENTITY PRIMARY KEY, Pad char(8000) NOT NULL);
INSERT dbo.GrowthDemo (Pad)
SELECT TOP (3000) 'x'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
GO
WAITFOR DELAY '00:00:02';
GO
WITH TargetData AS (
    SELECT CONVERT(xml, t.target_data) AS x
    FROM sys.dm_xe_session_targets AS t
    JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
    WHERE s.name = N'GrowthDemo' AND t.target_name = N'ring_buffer'
)
SELECT e.n.value('(data[@name="file_type"]/text)[1]', 'varchar(20)') AS FileType,
       e.n.value('(data[@name="size_change_kb"]/value)[1]', 'bigint') AS SizeChangeKb,
       e.n.value('(data[@name="total_size_kb"]/value)[1]', 'bigint') AS TotalSizeKb,
       e.n.value('(data[@name="is_automatic"]/value)[1]', 'bit') AS IsAutomatic,
       e.n.value('(data[@name="duration"]/value)[1]', 'bigint') AS Duration,
       e.n.value('(data[@name="file_name"]/value)[1]', 'nvarchar(128)') AS FileName,
       e.n.value('(data[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName
FROM TargetData
CROSS APPLY x.nodes('/RingBufferTarget/event') AS e(n)
ORDER BY e.n.value('(@timestamp)[1]', 'datetime2');

Read the IsAutomatic column. The first row is our manual resize, with 0. The data file then grew twice by itself, from 16384 to 24576 and then to 32768 KB, each time with IsAutomatic 1. The log file also grew by itself, by 65536 KB in my run.

That log growth is a surprise to many people. A big insert fills the log as well as the data file. The size of the log step depends on the growth setting your model database gives new databases, so your number may differ.

Two details help in a real investigation. First, the DatabaseName column came back empty here, so I use database_id and file_name to identify the file. Second, Duration is raw. Check its unit on your own build before converting it to seconds, and do not compare it across events until you have.

What to keep for real incidents

A ring buffer is a small, in-memory demo target. It disappears when you drop the session. For real incident history, write the events to a file target with a retention plan and a narrow filter like the one above.

Also, the event shows what grew and when. It does not name the application that caused it. Match its timestamp against your job history and your other monitoring before you blame anyone. Here is the cleanup for the demo.

USE master;
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'GrowthDemo')
    DROP EVENT SESSION GrowthDemo ON SERVER;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time a file grows on a Monday, read the flag before you start the blame game.

A bigger file is not autogrowth, it is a size change that needs a witness.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.