Tracking FileGrowth

I was always of the opinion that we should avoid using the “autogrow” option ON for production databases - whether small applications or large applications), simply because of the lot of overhead it creates in the expansion of the file, connection issues while the expansion happens, and most importantly, the amount of fragmentation it creates. Recently while going through a production issue, I heard this question from a fellow administrator that how we can find out when a file growth happened (This is a database where the autogrow is set to ON). I dug through some resources, but accidentally found out that is getting tracked in the default trace. We can just0 query the trace file to obtain the growth events. Joining the result with sys.Trace_Events will give us more clear results. Run the below given query and ensure that the path to the trace file is correct -
SELECT TrcEv.Name, Trc.DatabaseName, Trc.FileName, Trc.StartTime FROM fn_trace_gettable(E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_6.trc', NULL) AS Trc INNER JOIN sys.trace_events TrcEv ON Trc.EventClass = TrcEv.trace_event_id WHERE TrcEv.Name LIKE '%Auto Grow'
ORDER BY StartTime.
If you want to see, how the initial size of the file and the growth factor affects the file growth (and the index fragmentation of course), try the small example given below-

CREATE DATABASE GrowthTest
GO
USE GrowthTest
GO
CREATE TABLE dbo.Employee
(EmployeeName VARCHAR(3000))
GO

DECLARE @i INT
SET @i =1
WHILE @i < 10000
BEGIN
INSERT INTO Employee VALUES(REPLICATE('A', 3000))
END
(I assume that the filegrowth for your model database is the default settings) After this, run the above given query once more to see how many times the autogrowth happened.