1

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.

autogrowth data

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.

closed as too broad by Erik Darling, hot2use, RDFozz, mustaccio, MDCCL May 16 '18 at 0:08

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Why use auto-growth at all? Why not log your growth statistics and predict the next X months of growth, and increase your file to that size? – George.Palacios May 15 '18 at 8:49
  • If you feel like 200MB auto growth settings is not enough based on your experience, just increase it using an educational guess. Just make sure your storage space is enough for the expected auto growth events. – Edgar Allan Bayron May 15 '18 at 9:14
  • Could you provide additional details, why you want to modify these settings? As it stands there are a lot of "it depends" answers possible and the question is a bit too broad for a single all-encompassing answer. The community would appreciate it if you could edit your question and provide additional details if possible. – hot2use May 15 '18 at 13:12
  • The main reason we are planning to change the autogrowth factor is that we have observed, we are experiencing frequent upto 11 times auto grow a day atleast and also , many frequent CPU spikes which i fear is mainly due to the auto grow factor which has some role in performance degradation – Sayantani Nath May 16 '18 at 6:58
3

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.

  • Excellent scripts and my approach to getting this information. I would just go ahead and actually grow the file to the target size if growth events are so painful for the OP. – Jonathan Fite May 15 '18 at 13:05
  • Thank you for your feedback. There are a lot of "It depends..." answers possible here, because the information provided by OP is "too broad". I just wanted to get this quick win information planted, before providing feedback to the author. – hot2use May 15 '18 at 13:10
  • Hi , Can anyone suggest how to make sure if Instant File Initialization is turned on ? If not , then how to do it ? Also , when i execute the first script shared by @hot2use , the result shows null in the 'GrowthMB' column, Can you clarify why this is happening. – Sayantani Nath May 16 '18 at 7:01
  • There should be two lines for each database. The first line is the base for calculating the value that will be returned in the second line. – hot2use May 16 '18 at 7:05
6

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

  • We are using SQL SERVER 2012 RTM version (Standard). 'please get them onto separate drives.' -Could you explain what you mean by this ? – Sayantani Nath May 16 '18 at 6:38
  • Also, How much should the log file be changed ? (in MB i mean ) . Our DB size is 2 TB and currently its set to 10%, restricted to 2048 gb. – Sayantani Nath May 16 '18 at 7:20
  • @SayantaniNath if possible the MDF and LDF files should be on different physical drives, so they are not fighting for disk resources. Not knowing anything else, I would set the LDF autogrow to 256 or 512MB instead of %. – Kevin3NF May 16 '18 at 13:06
  • Hi @kevin3NF, how much should i keep the autogrow maximum file size to ? To maximum disk size ? Fllegrowth if i set to 512 mb ? – Sayantani Nath May 17 '18 at 11:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.