/*  Step 1 - Capture a Profiler or server side trace with these events:

      Event 41 = SQL:StmtCompleted

      Event 45 = SP:StmtCompleted

      Event 92 = Data file auto growth event

      Event 93 = Log file auto growth event

* Make sure to include these columns:

      ApplicationName,EventClass,DatabaseID,HostName,LoginName,

      ObjectID,SPID,StartTime,EndTime,TransactionID,TextData,

      Reads,Writes,CPU,Duration

*/   

--    Step 2 - Load the trace into a table:

SELECT*INTO TraceTable FROM::fn_trace_gettable(<<path to trace file>>,DEFAULT)

GO

--    Step 3 - Make a column to hold part of the textdata for search purposes

ALTERTABLE TraceTable ADD QueryText VARCHAR(MAX)

GO

UPDATE TraceTable SET QueryText =CONVERT(VARCHAR(MAX), TextData)

GO

--    Step 4 - Get all spids and trasactionids that are associated with auto growth

SELECTDISTINCT SPID, TransactionID

INTO #GrowTrans

FROM TraceTable

WHERE EventClass IN(92, 93)

GO

--    Step 5 - Get the info on statements called by the spid and transactionid associated with auto growth

SELECT t.*

FROM TraceTable t

JOIN #GrowTrans g ON (t.SPID = g.SPID AND t.TransactionID = g.TransactionID)

ORDERBY StartTime

GO

 _________________________________________________________________