Finding last database growth date and time

A file growth operation on a database server is a extremely expensive operation. Many Performance problems correlate to a Data or log file growth event on the database. As a result its extremley important for a DBA, to have a way to check the time of Data or Log file growth occured earlier. A good way of finding it will be thro SQL default trace. Refer to the following query


DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC 




The script provided above will list the databases that grew recently along with their time of growth. The script pulls these details from SQL Server''s default trace. Default trace is a light weight trace running at the background by default capturing many important events. Few of them are listed below.

Data File Auto Grow
Data File Auto Shrink
Database Mirroring State Change
ErrorLog
Full text crawl related events
Hash Warning
Log File Auto Grow
Log File Auto Shrink
Missing Column Statistics
Missing Join Predicate
Object:Altered
Object:Created
Object:Deleted
Plan Guide Unsuccessful
Server Memory Change
Sort Warnings

SQL default trace can write upto 5 files with each file with a maximum size 0f 20 MB.
So maximum log of 100 MB will be generated. So, the amount of time SQL Trace retains the log details depends on the amount of activity on the server.One can change the same script to view other events and get other useful info like 'who altered that object?' , 'Who granted the rights to a particular table' and so on.

For more details on default trace refer here and here and here