locked
Auto growth in sql server 2016 RRS feed

  • Question

  • What is the impact if i disable the Autogrowth option in SQL Server 2016?

    Monday, August 3, 2020 7:25 AM

Answers

  • Hi VijayKSQL,

    If you disable autogrowth option, you may face the application modification failure caused by the database space problem or SQL Server is running out of hard disk space. Meanwhile the autogrowth of data files and log files itself may consume system resources and affect performance. 

    So it depends if you would like to disable the Autogrowth option.

    But it would be better for you to set up the Autogrowth with optimized settings like below rather than disable it.

    (1) Set the grow at a fixed size, not by percentage.
    (2) Regularly monitor the usage of each data file, and try to ensure that the remaining space of each file is the same size or the expected proportion.
    (3) Set the maximum file size to prevent SQL Server files from running out of disk space due to autogrowth and affecting the operating system.
    (4) After autogrowth occurs, check the new data file space allocation in time. 

    Please also refer below link for more details:

    Auto Growth Setting Performance Impact in SQL Server

     

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Wednesday, August 5, 2020 8:22 PM
    Monday, August 3, 2020 9:17 AM

All replies

  • What is the impact if i disable the*Autogrowth *option in SQL Server 2016?

    The impact is that if you run out of space in the file in question, all write operations will fail with error that there is not any space to write the data or the log records.

    If you have autogrowth enabled, the file will grow in size and the write operation will succeed. With one very important qualification: that there is disk space enough available for the growth you have configured. If there is not, there will still be an error about lack of space.

    Autogrowth is a good safeguard, but, ideally, autogrowth should never occur, but you should grow your files in advance during maintenance windows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 3, 2020 7:57 AM
  • Hi VijayKSQL,

    If you disable autogrowth option, you may face the application modification failure caused by the database space problem or SQL Server is running out of hard disk space. Meanwhile the autogrowth of data files and log files itself may consume system resources and affect performance. 

    So it depends if you would like to disable the Autogrowth option.

    But it would be better for you to set up the Autogrowth with optimized settings like below rather than disable it.

    (1) Set the grow at a fixed size, not by percentage.
    (2) Regularly monitor the usage of each data file, and try to ensure that the remaining space of each file is the same size or the expected proportion.
    (3) Set the maximum file size to prevent SQL Server files from running out of disk space due to autogrowth and affecting the operating system.
    (4) After autogrowth occurs, check the new data file space allocation in time. 

    Please also refer below link for more details:

    Auto Growth Setting Performance Impact in SQL Server

     

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Wednesday, August 5, 2020 8:22 PM
    Monday, August 3, 2020 9:17 AM