Autogrowth settings in SQL server

Question

how should we setup the autogrowth as it mainly depend on database size.
If a database size in 256mb we won’t setup an autogrowth to 2 gb.
But if a database is 2tb, we won’t setup an autogrowth to 32mb

Answer 1

how should we setup the autogrowth as it mainly depend on database size.

It would be incorrect to say that autogrowth depends on DB size. What you are saying is just one small part for sake of argument. You need to query the autogrowth events and check how frequently and in what amount they are growing and then set that as auto growth value. Ideally when database is created people normally create it of large size so as to avoid autogrowth events, which ofcourse are costly.

Look at the section Identifying How Often an Auto-growth Event has Occurred in blog sql-server-database-growth-and-autogrowth-settings. But I would suggest you to read the whole blog.

Auto growth should be set in MB, avoid setting it in percentage.

Answer 2

Hi @Shashikala Bhat ,

Best Practices for Database Autogrowth Property.

1. Analyze your database growth pattern and set optimum size of database with the analyzed growth settings. Your database should have enough size that it never grows and if it grows make sure that Instant file initialization should be enabled on that SQL Server Instance. This will drastically reduce the Autogrowth events in day to day life.
2. Proactively monitor database file sizes and their autogrowth events. This helps you to analyze the growth pattern of database files and avoid fragmentation.
3. Consider defragmenting your database file system if you have lot of auto-growth events occurred on your databases.
4. Never leave your database autogrowth with default values, change it as per growth pattern that you analyzed over time.
5. Avoid using autogrowth in percentage, rather give a specific amount of size in MB/GB. Microsoft suggests to set your autogrow setting to about one-eighth the size of the file and test it before deploying on PROD.
6. Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
7. The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Even with autogrow turned on, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query.

Please refer to the blog Understanding Database Autogrowth in SQL Server to get more detail information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.