Best practices to set database auto growth size on SQL server

Category: Reliability

Item:  File growth options

What is Database Autogrowth in the sql server?

It’s a procedure used by the SQL Server engine to expand the database size when all its space runs out.

If the auto-growth setting for a database is not set correctly, the database may experience various or few auto-grow events.

When SQL Server has to grow a file, all transactions stop. They wait until file growth operation is complete to continue.

These events can introduce unpredictable hits in performance at random times (especially if disks are performing slow).

How do I configure the settings?

A good practice is to change all database file growth options by large enough MB value instead of a percentage value or low number such as 1MB.

You can use the query below to generate a change script:

  1. Change the growth increment [XXX] that must be large enough to avoid performance penalties.
  1. SELECT 'ALTER DATABASE [' + db_name(s.database_id) + '] 
  2.       MODIFY FILE ( NAME = N''' + s.name + ''', FILEGROWTH = XXXMB)' as ToExecute
  3. FROM sys.master_files s
  4. INNER JOIN sys.databases db ON s.database_id = db.database_id
  5. where (s.is_percent_growth = 1
  6. or s.growth * 8.0 / 1024 < 10)
  7. and db.state_desc = 'online'
  8. ORDER BY s.database_id

The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment.

Figure 1- Sample of the query output.

Microsoft’s best practices general rule for testing is to set your autogrow setting to about one-eighth the size of the file.

More information

Microsoft – Considerations for the “autogrow” and “autoshrink” settings in SQL Server

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

One Response

  1. Hi Jose,
    Thanks for your information.

    Could you please explain
    Standard Value of Auto growth DATA and Log File Size.

    and also Initial Size

Leave a Reply

Your email address will not be published. Required fields are marked *