When did the database grow?

Ever had a database that suddenly and unexpected grow very much? If it happens during the night you may want to know in wich of all night batches it accured. One quick way to figure it out is by using the built in reports in SQL Server Management Studio (SSMS). Just right klick on the database an view the report “Disk Usage”. Right under the two pie charts (showing total space usage) you can open a list of AutoGrow/AutoShrink events and see exactly when it grow.

If you, for some reason, can’t use the report you can find it your self with the following piece of code. The information is stored in SQL Servers autotrace, a trace which can be very handy in certain situations. DECLARE @tracefilename VARCHAR(500) ; DECLARE @tracename VARCHAR(500) ; DECLARE @i int ; SELECT @tracefilename = [path] FROM sys.traces WHERE is_default = 1 ; SELECT @tracename = LEFT(@tracefilename,LEN(@tracefilename) - PATINDEX('%%', REVERSE(@tracefilename))) + 'log.trc' ; SELECT tf.DatabaseName, tf.Filename, te.name AS Event, tf.StartTime, tf.EndTime, (tf.IntegerData*8.0/1024) as ChangeMB FROM ::fn_trace_gettable( @tracename, DEFAULT ) tf LEFT JOIN sys.trace_events te ON tf.EventClass = te.trace_event_id WHERE tf.EventClass BETWEEN 92 AND 95 AND tf.DatabaseName = DB_NAME() ORDER BY tf.StartTime;