July 25, 2012 at 10:27 am
Looking for "standards" or opions : Which is better and why?
I have always a file creation size, file growth amount and max file size. Usually this is 1st based on BU/Vendor/Analyst knoweldge of what to expect. AS the proceses evolve and testing is done, we almost always change the values. By the time we create production, we usually have a good idea on sizes. Like, max size is usually set to what the estimated DB size is after 1.5 to 2 years. Incremental growth ranges from 10meg to 4096mg depending on activity.
part of the reason for max file size for me; is that if a process goes awry, i rather a single file fill up and not the entire drive.
This is based on experience and usage. I am working with an consulting company that is taking over production responseibilities. The 1st thing they did is set all db to unlimited file size.
Is there a write / wrong? Or is it experience and opinion?
Joe
July 25, 2012 at 1:47 pm
joseph.devereaux (7/25/2012)
Looking for "standards" or opions : Which is better and why?I have always a file creation size, file growth amount and max file size. Usually this is 1st based on BU/Vendor/Analyst knoweldge of what to expect. AS the proceses evolve and testing is done, we almost always change the values. By the time we create production, we usually have a good idea on sizes. Like, max size is usually set to what the estimated DB size is after 1.5 to 2 years. Incremental growth ranges from 10meg to 4096mg depending on activity.
part of the reason for max file size for me; is that if a process goes awry, i rather a single file fill up and not the entire drive.
This is based on experience and usage. I am working with an consulting company that is taking over production responseibilities. The 1st thing they did is set all db to unlimited file size.
Is there a write / wrong? Or is it experience and opinion?
Joe
I'll venture that the best practice is monitor free space and grow the file manually.
Auto-growth is insurance and shouldn't be relied upon.
Setting a max size should be insurance against out of control file growth affecting other DB's.
If you're using auto-growth, then you should have notifications setup to alert you when auto-growth happens.
Setting a max size or not isn't as important as being proactive in growing your files.
Just my two cents.
July 26, 2012 at 5:36 am
I came into a situation where the number of dba is 3 and the number of production systems alone is over 600. I was pursueing going down the path of SQL Alerts with email from the db side and using Solar Winds notification from an physical disk (OS) side.
I have found it much easier and faster to increase the max size (if something runs amuck) that the 2 to 5 hours it takes to expand drives, if and when possible.
I am currently writing up the standard for the max size of the database files and amount of disk on each drive.
Thank you.
July 27, 2012 at 8:16 am
1) I recommend to all my clients to actually SIZE their files to allow for 12-18 months of EXPECTED database total size IN ADVANCE and then monitor monthly. An autogrowth should be a mistake, not a means of managing file sizes.
2) If you are more concerned about not losing more than one of your databases by filling up a disk than about more-frequently causing a single database to go offline, you can set a max size. Otherwise I would warn against that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 27, 2012 at 10:21 am
Agree with everyone's replies and wanted to add the obvious tidbit: autofilegrowth introduces a significant amount of contention (as it is a very costly IO activity) and depending on how your DB's are spread across your spindles it can have adverse affects on the rest of your server.
So it isn't "wrong" to set autogrowth on, but its prolly considered "bad practice" if your DB isn't size appropriately and the growth occurs on a regular basis.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply