Query Auto Grow Settings of a SQL Database
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