Recommended Auto Grow settings suitable for database whose size is 867666.25 MB

Question

I need recommendations for setting/modifying the auto grow options for our production database.

size of FUNDSDB 867666.25 MB

Also, I have attached the current autogrowth statistics , looks like it is set to 200 MB which I think is considerably less considering the database size and also it should be modified to some realistic value else these auto-growth events can cause performance degradation for our current db transactions.

Can anyone suggest the auto growth settings best suitable for this heavy OLTP production database? We are using SQL Server 2012 standard RTM version currently. The main reason we are planning to change the autogrowth factor is that we have observed, we are experiencing frequent up to 11 times auto grow a day and also, many frequent CPU spikes.

Answer 1

Set it to 1024MB. If it is still happening too often, then 4096MB. Until you measure your actual growth needs, it guesswork.

Or, manually grow it to 950GB, or an even 1TB and let Autogrow not be a factor, which is what you should be doing anyway.

Is Instant File Initialization turned on? You didn't mention the version of SQL Server.

And change the Log file from % to MB.

And just for bonus - please get them onto separate drives.

Kevin3NF

Answer 2

You might want to consider analysing the growth of your database over the last n days. This can be achieved by analysing the backup information in the msdb database. The following scripts are two variations of how to achieve this:

-- Transact-SQL script to analyse the database size growth using backup history. 
DECLARE @endDate DATETIME,
        @months SMALLINT; 
SET @endDate = GETDATE();  -- Include in the statistic all backups from today 
SET @months = 6;           -- back to the last 6 months. 
;
WITH HIST AS 
     (
         SELECT BS.database_name AS DatabaseName,
                YEAR(BS.backup_start_date) * 100 
                + MONTH(BS.backup_start_date) AS YearMonth,
                CONVERT(NUMERIC(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB,
                CONVERT(NUMERIC(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB,
                CONVERT(NUMERIC(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
         FROM   msdb.dbo.backupset AS BS
                INNER JOIN msdb.dbo.backupfile AS BF
                     ON  BS.backup_set_id = BF.backup_set_id
         WHERE  NOT BS.database_name IN ('master', 'msdb', 'model', 'tempdb')
                AND BF.file_type = 'D'
                AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
         GROUP BY
                BS.database_name,
                YEAR(BS.backup_start_date),
                MONTH(BS.backup_start_date)
     )

SELECT MAIN.DatabaseName,
       MAIN.YearMonth,
       MAIN.MinSizeMB,
       MAIN.MaxSizeMB,
       MAIN.AvgSizeMB,
       MAIN.AvgSizeMB 
       -(
           SELECT TOP 1 SUB.AvgSizeMB
           FROM   HIST AS SUB
           WHERE  SUB.DatabaseName = MAIN.DatabaseName
                  AND SUB.YearMonth < MAIN.YearMonth
           ORDER BY
                  SUB.YearMonth DESC
       ) AS GrowthMB
FROM   HIST AS MAIN
ORDER BY
       MAIN.DatabaseName,
       MAIN.YearMonth

Reference: Database size growth as a list (Microsoft Technet)

Or alternatively use the following script:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO

Reference: Identify SQL Server Database Growth Rates (mssqltips.com)

Based on the information provided with these scripts you could then set the Growth Setttings of your database to be near the GrowthMB (Microsoft Technet Script) value or near the Max Size Diff From Previous (MB) value (Mssqltips.com Script).

If you interpolate into the future, you could possibly set the Max Size of the database files (*mdf) to be a multiple of the returned values (12 month worth?) plus the current DB size.