SQL SERVER – Script – When/Who did Auto Grow for the Database?

What happens when you try to copy a large file from different hard disks? The operating system tries to see if the destination location has enough space available for the copy to be successful. If we don’t have enough space, then it is second to human nature to make space and restart the copy operation. I am sure most of us who use USB drives have done this task. If you look at SQL Server, it does a similar act. When there is no space in the file while writing, SQL Server goes ahead and expands the file based on our configuration. But if you are a seasoned DBA, you would want to know when these expansions happen. This blog is inspired by a question asked by one of the blog readers. All these questions are a major source of my blog content. Recently I received below email:

We see the database file size getting increase automatically every day. We are not having many active users, or transactions in the database. I read your blogs and understood that this might be due to auto grow setting of the database. Is there any way to find “who” is doing it?

The problem is half solved if we understand the reason. DBA has already nailed it down till auto growth and to help my reader further I provided him below script to find the auto growth events happen on the server. This information is pulled from Default Trace which runs by default on every SQL Server installation.

Here is the script to get the auto grow events which happened of database “SQLAuth”. Please change it as per database name in your environment

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SET @database_name = 'SQLAuth'
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 created a dummy database, insert some rows so that auto growth is caused. Then check the report.
CREATE DATABASE [SQLAuth] GO
ALTER DATABASE [SQLAuth] SET RECOVERY FULL
GO
BACKUP DATABASE [SQLAuth] TO DISK = 'NUL'
GO
USE [SQLAuth] GO
CREATE TABLE PinalDave (Pinal INT ,Dave CHAR(8000))
GO
SET NOCOUNT ON
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 10000
BEGIN
INSERT INTO PinalDave
VALUES (1,'Pinal Dave')
SET @i = @i + 1
END


Here is the output of the script which shows the growth event details.

Reference: Pinal Dave (https://blog.sqlauthority.com)