How to check when autogrowth is done last?

Question

In sql server 2005, the autogrowth is enabled by size. Is there any way to check when autogrowth on data and log file happened last?

Solution 1

SSMS, right click your db, go to reports->standard reports->disk usage and look for Autogrow/Autoshrink events .

Hopefully you have the correct trace levels set up, if not you might have some issues finding out history.

Solution 2

Here's how to do it without using the sql reports(link, followed by relevant TSQL): https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
       WHEN 92 THEN 'Data'
       WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;