Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    32

    Unanswered: Autogrowth for MDF and LDF files

    I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

    Thanks,

    Carlos

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by carloarango
    I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

    Thanks,

    Carlos

    I generally allow dbs that are smaller that 20 GB to autogrow by the default setting (10%). But once they get above that size, I manage them manually and ensure that there is enough empty space in the datafile to get through until the next maintenance window.

    Growing a data file in SQL 2005 is not as expensive (IO wise) as it was in SQL 2000, but I still think you want to keep a closer eye on things once they get above 20 GB. 20 GB is admittedly arbitrary. If you have space issues, you might consider a lower threshold.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It depends on what your database is used for. Mine are configured to grow by several hundred MB\ a few GB but that is because there are small numbers of massive modifications. An OLTP database should not, IMHO, be growing that much each time. The user that submitted the modification that triggers a 500MB growth could be twiddling their thumbs for quite some time cursing the system as they do.

    I too would manage the growth at peak periods with a view to eliminating\ reducing autogrowth as much as possible.

  4. #4
    Join Date
    Oct 2007
    Posts
    35
    Your file growth is because of your 500MB setting. When SQL starts to run out of space it will adjust by 500MB. If has to adjust twice a day, there's your 1GB. I think you're fine where you're at. Just make sure you have enough drive space. I'm sure your growth will plateau.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •