Menu
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