What’s Causing Files to Automatically Grow

By SQLRx Admin | Helpful Scripts

Mar 23

Every DBA has encountered a database that has a mystery statement that will unexpectedly cause either the transaction log or a table to grow out of control and fill the drive.  Here is a way to find out who is running what statement that is causing the problem.  Find out what statements are causing database files to automatically grow.

 /*  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

About the Author

>