May 31, 2011 at 12:26 pm
What is the better option/practice to setup the maximum file size, to restricted file growth, or Unrestricted File Growth?
May 31, 2011 at 8:37 pm
This depends on a number of factors. I've seen recommendations to grow the files to fill the disk so autogrowth isn't needed. If possible, that would be preferred to unlimited growth but it relies on that space not being needed for anything else such as other databases, backups or any other files. If there's a chance that space could be used for something else then you're best keeping the database files smaller and allowing growth.
Whether or not to restrict the growth depends on the repercussions of not being able to grow. If the DB isn't a business critical DB and shares space with a critical DB then you may want to restrict it to make sure it doesn't interfere with the critical database.
In the end, the best thing is to know your environment and size the databases properly so they don't need to auto grow. Monitor them so you can plan growth out ahead of time and know when you're going to need more space, both for the DB and on the disk.
May 31, 2011 at 9:48 pm
I grow my files to a size where they won't need to auto grow for a year to 1.5 years. Like the other poster said, you do not want your databases to auto grow ever. Treat it like a video game where the better you do at your job, the higher your salary score.
Growth should happen in large chunks and once a year, unless you didn't plan properly or the servers you maintain are undersized and you don't have the disk space for that (in which case, study up on the reasons why then fight with your boss).
As for capping growth, this is most beneficial when you have multiple files in a file group on different disks. If the files don't grow evenly, or if one of the disks has something else on it that prevents you from allowing the files to evenly fill, then you may choose to cap one file so that it doesn't try to auto grow mid-day and error out because the disk is full. By capping one file, the data filling will 'overflow' (with no errors) onto another file in the file group (depending on how your file groups are configured).
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 31, 2011 at 10:23 pm
Autogrow should only be a "fail-safe" type of operation at best. One big reason that many prefer not to allow autogrow is the heavy IO cost that comes with it. You don't want that to happen during business hours or during a bulk load or some other process that is time sensitive.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply