December 4, 2012 at 8:26 pm
Comments posted to this topic are about the item Get Rid of All Those Pesky 1 MB File Growth DB Files
G. Milner
December 5, 2012 at 1:32 am
Now to tweak the script to get rid of all those pesky 10% file growth db files:-D
December 5, 2012 at 4:39 am
Yes, 10% growth is a horrible setting as well.
Try to grow with fixed size chunks of 256MB-1024MB instead (depending on your database growth expectation).
For log files, set an upper limit and never ever let a log file grow indefinetly! Transactions need to be short and if some screw up code runs out of control, you want a hard limit to stop it before it fills up all your storage and crash your server (talking simple recovery model here).
What is true for logfiles is true for tempdb as well, limit its size, but you can be generous there (10-20GB)!
These are (my) rules of thumb and i review them per database i deploy, every case can be different! It is not something you can script with always a good outcome.
December 5, 2012 at 7:46 am
I know 10% growth is a bad setting. That's why I said "Later you can go about tweaking them individually." The point is just to show you can run something in all databases to get rid of the 1 MB growth setting. If you wanted to change the script to make the growth a hard amount, that's your business, but I am assuming you would want to grow each databases based on various factors, including your personal knowledge of how the databases is used -- not something I could include easily in this script.
I am not necessarily advocating leaving the databases at 10% growth.
G. Milner
December 5, 2012 at 7:09 pm
Yeah, the 10% is awful. I'm afraid I don't see the point of resetting to something almost as bad.
Why not create default growth tables based on total size, one for data and one for logs? Typically a size of anywhere from 5-20% works well for data and/or logs, modified to take into account existing file sizes, disk space available, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 5, 2012 at 7:49 pm
Yes, 10% is not good. Still, for a 10 to 100 GB database, 10% is a LOT better than the default of 1MB and as I said "Later you can go about tweaking them individually." and further, "I am assuming you would want to grow each databases based on various factors, including your personal knowledge of how the databases is used -- not something I could include easily in this script."
The point of this script is to show how you could use sp_MSForEachDB (undocumented SP, I know) to do this. As always, you are free to adapt it as you see fit.
G. Milner
December 6, 2012 at 8:04 am
I don't mean to be mean or scold someone that offers up a script to help people. But, yes percentage growth is about as bad. Microsoft even says that in some articles but then they ship SQL install with percentage growth. When I install SQL I go ahead and change all system dbs to be sized larger and change the growth to something larger instead of 1MB or 10%.
March 13, 2013 at 4:18 pm
Occasionally a product specialist will deploy a new SQL Server, and then add it to my list of servers to maintain. So run into this scenario.
I think that I can modify your script to the auto-growth step size of my liking. So I won't assume that you don't know best practices, or offer advice on what I think they are, and just say thanks for the help!
March 13, 2013 at 7:33 pm
Thanks, Mike. Glad to have been of help. I know 10% is not Best Practice, of course. But I had to have some value that was better than 1 MB, so that's what I chose. It was totally arbitrary.
G. Milner
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply