Interesting Discussion so far.
My opinion is that autogrowth should be considered a contingency feature. Every time there is a growth there will likely be disk fragmentation as a result, so autogrowth should be kept to a minimum and databases sized with sufficient growth for a decent period of time. Fewer growths = less disk fragmentation.
So for me whilst i find it useful for capacity planning how big the database is over time, i tend to focus more on avoiding autogrowths and detecting them. This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.
USE [master]
GO
/****** Object: View [dbo].[vw_DBA_FileAutoGrowths] Script Date: 06/02/2009 19:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_DBA_DefaultTraceFilename]() RETURNS varchar(255) AS
BEGIN
DECLARE @Filename varchar(255)
SELECT @Filename=LEFT(t.Path,LEN(t.Path)-CHARINDEX('\',REVERSE(t.Path)))+'\log.trc'
FROM sys.traces t
CROSS JOIN sys.configurations c
WHERE c.name='default trace enabled' AND c.value=1
RETURN @Filename
END
GO
CREATE VIEW [dbo].[vw_DBA_FileAutoGrowths] AS
/*
Purpose: Returns a list of auto-growths and auto shrinks that have occurred across all the databases
Author: Michael I Wade
Created: 24 December 2008
Dependencies: dbo.udf_DBA_DefaultTraceFilename
*/
SELECT t.DatabaseName,
t.StartTime,
t.EndTime,
CASE WHEN t.EventClass IN (92,94) THEN 'Data File' ELSE 'Log File' END FileType,
CASE WHEN t.EventClass IN (92,93) THEN 'Auto-Grow' ELSE 'Auto-Shrink' END ChangeType,
t.[Filename],
t.IntegerData*8.0/1024 ChangeInKB,
t.ApplicationName,
t.LoginName
FROM ::fn_trace_gettable(dbo.udf_DBA_DefaultTraceFilename(),default) t
WHERE t.EventClass IN (92,93,94,95)
GO
Calling SELECT * FROM vw_DBA_FileAutoGrowths
will tell you all the recent growths and what application / login caused it (This bit is handy for those annoying users who do stupid things like carteasean join inserts!)
Feel free to use my code, but just give me credit if you republish it 🙂