Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get Rid of All Those Pesky 1 MB File Growth DB Files


Get Rid of All Those Pesky 1 MB File Growth DB Files

Author
Message
Greg Milner
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 491
Comments posted to this topic are about the item Get Rid of All Those Pesky 1 MB File Growth DB Files



G. Milner
P Jones
P Jones
Right there with Babe
Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)Right there with Babe (766 reputation)

Group: General Forum Members
Points: 766 Visits: 1510
Now to tweak the script to get rid of all those pesky 10% file growth db files:-D
peter-757102
peter-757102
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 2551
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.
Greg Milner
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 491
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
ScottPletcher
ScottPletcher
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4315 Visits: 6798
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Greg Milner
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 491
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
Markus
Markus
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1820 Visits: 3728
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%.



mike.schmid
mike.schmid
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 87
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!
Greg Milner
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 491
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search