Thomas Stringer's points are all you need for your answer. But I would put some more insight on how you determine your autogrowth settings ?
But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database. Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?
There is no mathematical formula to calculate your autogrowth settings, especially when you do not do a baseline of you databases.
Now, as @ThomasStringer pointed out that you should not allow your database autogrowth to be in %, rather set it to MB, you can find out Autogrowth events happening on your server instance using the default Trace.
--below code will help you in finding autogrowth events on your server instance.
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] AS LogicalFileName
,(ftg.IntegerData * 8) / 1024.0 AS GrowthMB
,(ftg.duration / 1000) AS DurMS
,mf.physical_name AS PhysicalFileName
INTO #autogrowthTotal
FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
INNER JOIN sys.master_files mf ON (mf.database_id = ftg.databaseid)
AND (mf.NAME = ftg.[FileName])
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
,[LogicalFileName]
,PhysicalFileName
,SUM(GrowthMB) AS TotalGrowthMB
,SUM(DurMS) AS TotalDurationMS
INTO #autogrowthTotal_Final
FROM #autogrowthTotal
GROUP BY CONVERT(VARCHAR(10), StartTime, 120)
,EventName
,DatabaseName
,[LogicalFileName]
,PhysicalFileName
--having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
ORDER BY CONVERT(VARCHAR(10), StartTime, 120)
SELECT *
FROM #autogrowthTotal_Final
Below will be the output
Note: I have highlighted in the image what each column means and what you should look for.
Basically, you have to monitor your autogrowth events for a duration of time e.g. during high activity or for you entire business cycle and then averaging it will give you a some what exact value that you can choose for autogrowth settings.
Now, for Log file, you also have to consider factors like Index maintenance, CHECKDB running, etc. So size the log file to support the volume of data changes occurring in the database and take log backups frequently so as to allow rapid reuse of space within the log file.
Also, worth to mention that you should enable Instant File Initialization as well. Works only for Data files !
Refer to Importance of data file size management esp the Data file growth and Data file shrinking by Paul Randal.
Note: Do not shrink your database, unless you do a massive purge of your data and you are sure that the database is not going to grow that big again. It causes fragmentation and databases are meant to grow !
