Have you ever wondered why the size of your SQL Server database files keeps increasing automatically, even when there are not many active users or transactions? This blog post aims to shed light on this common question asked by many database administrators.

When SQL Server runs out of space while writing data to a file, it automatically expands the file based on the configured auto growth settings. This behavior is similar to what happens when you try to copy a large file from one hard disk to another. The operating system checks if the destination location has enough space, and if not, it expands the file to accommodate the copy operation.

As a seasoned DBA, it is important to understand when these auto growth events occur and who is responsible for triggering them. To help you with this, I have prepared a script that retrieves auto growth events from the Default Trace, which is enabled by default on every SQL Server installation.

Here is the script to get the auto growth events for a specific database:

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;

SET @database_name = 'YourDatabaseName';

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

SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';

SELECT DatabaseName, Filename, (Duration / 1000) AS 'TimeTaken(ms)', StartTime, EndTime, (IntegerData * 8.0 / 1024) AS 'ChangeInSize MB', ApplicationName, HostName, LoginName
FROM::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
AND DatabaseName = @database_name
AND (d.create_date < EndTime)
ORDER BY t.StartTime DESC;

To test the script, you can create a dummy database, insert some rows to trigger auto growth, and then check the report.

CREATE DATABASE [YourDatabaseName]
GO

ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL
GO

BACKUP DATABASE [YourDatabaseName] TO DISK = 'NUL'
GO

USE [YourDatabaseName]
GO

CREATE TABLE DummyTable (ID INT, Name VARCHAR(50))
GO

SET NOCOUNT ON
GO

DECLARE @i INT
SET @i = 1

WHILE @i < 10000
BEGIN
    INSERT INTO DummyTable VALUES (@i, 'Name' + CAST(@i AS VARCHAR(10)))
    SET @i = @i + 1
END

The output of the script will provide you with detailed information about the auto growth events, including the database name, file name, duration, start time, end time, change in size, application name, host name, and login name.

By understanding when and why auto growth events occur, you can better manage your SQL Server databases and ensure optimal performance. It is important to regularly monitor and adjust the auto growth settings based on your database’s growth patterns to avoid unexpected file expansions.

Remember, being proactive in managing your SQL Server databases will help you maintain a healthy and efficient system.