Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get Rid of All Those Pesky 1 MB File Growth DB Files Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 8:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 114, Visits: 456
Comments posted to this topic are about the item Get Rid of All Those Pesky 1 MB File Growth DB Files



G. Milner
Post #1392769
Posted Wednesday, December 5, 2012 1:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 559, Visits: 1,203
Now to tweak the script to get rid of all those pesky 10% file growth db files
Post #1392829
Posted Wednesday, December 5, 2012 4:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:06 AM
Points: 330, Visits: 2,249
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.
Post #1392916
Posted Wednesday, December 5, 2012 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 114, Visits: 456
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
Post #1393015
Posted Wednesday, December 5, 2012 7:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 2,192, Visits: 3,299
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1393289
Posted Wednesday, December 5, 2012 7:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 114, Visits: 456
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
Post #1393294
Posted Thursday, December 6, 2012 8:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 1,289, Visits: 2,842
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%.


Post #1393541
Posted Wednesday, March 13, 2013 4:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 14, 2013 3:11 PM
Points: 11, Visits: 83
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!
Post #1430665
Posted Wednesday, March 13, 2013 7:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:59 PM
Points: 114, Visits: 456
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
Post #1430708
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse