Taking into consideration what @MikeWalsh and @KookieMonster, I would suggest you to monitor your autogrowth events overtime and then go with a number that suits your environment - Applies to both Data and Log files (By getting a number through monitoring and keeping a 10% buffer, it will help you minimize the number of autogrowth events that kicks in due to poor autogrowth settings).
Auto-growth events are expensive operations that slow down the performance of your database. Always, pre-Size your databases and establish appropriate autogrowth settings for your databases based on their growth profile.
One more time to stress, enable Instant File Initialization so Data files can leverage its awesomeness.
Below is how you can check the autogrowth events using the default Trace (which is running by default - unless you have turned it off. You can adjust the script by changing -- change this AS PER YOUR NEED !!)
set nocount ON
if exists (SELECT 1 FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2)
begin
IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
DROP TABLE #autogrowthTotal_Final;
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);
-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;
-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));
-- set filename without rollover number
SET @filename = @bfn + @efn
-- process all trace files
SELECT
ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.[FileName]
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92 -- Data File Auto-grow
OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime
select count(1) as NoOfTimesEventFired
, CONVERT(VARCHAR(10), StartTime, 120) as StartTime
, EventName
, DatabaseName
, [FileName]
, SUM(GrowthMB) as TotalGrowthMB
, SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [FileName]
having count(1) > 50 -- change this AS PER YOUR NEED !!
or SUM(DurMS)/1000 > 60 -- change this AS PER YOUR NEED !!
order by CONVERT(VARCHAR(10), StartTime, 120)
if exists (select * from #autogrowthTotal_Final)
begin
select * FROM #autogrowthTotal_Final
end
else
begin
SELECT ' There are minimal to none autogrowth events and these wont affect Database server performance. ' AS COMMENTS
,' Your databases are properly sized. Keep up the good work !! ' AS RECOMMENDATIONS
end
end
else
begin
SELECT ' There is no Default trace enabled. Check [value_in_use = 1] for [sp_configure default trace enabled]. ' AS COMMENTS
,' Please enable the Default trace to do Detailed analysis for Auto Growth Events as no information could be obtained as Default trace is not enabled !! ' AS RECOMMENDATIONS
end
set nocount OFF
Below is the output from one of my sandbox that I play around with Autogrowth :
