April 23, 2012 at 7:45 am
Hi,
What is the best pracise in mainaining autgrouth value for user and tempDB Databases.
For large Db and as well as small DB's........
Thanks In Advance.
April 23, 2012 at 7:56 am
Take a look at the following link:
http://msdn.microsoft.com/en-us/library/ms175527.aspx
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 8:07 am
Thank you for the link. Like User DB's waht is the best practise in chosing this autogrouth value . Ex: Fo 60 Gb Db with one .mdf, .ndf, 4 .ndf's......
April 23, 2012 at 8:10 am
I'm sure you can Google that!
Hopefully you'll realise it's on a case-by-case basis with many different factors contributing to your strategy on how / when to grow a database.
Here's another link.
http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 8:21 am
And be very aware that growth by percentage is almost never OK. If your db is small and growing, it can fragment the hell out of the database, the log, and the underlying physical files. If it's too big, it can cause growth-stalls and log files that are difficult to "recycle".
I don't have the link just now that explains it all but a growth of about 512 MB for log files seems to produce a good balance for the VLFs. Growth for data files probably shouldn't be less than 100MB for databases nor more than a gig or 2. I'll typically use 100MB for databases that aren't growing by leaps and bounds and 500MB for those that are (along with reserving a good amount of space for anticipated growth because growth really shouldn't take you by surprise).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 8:27 am
That's really interesting Jeff. Years ago I went on a MCP course for SQL 2000. The instructor recommended 50% (!!!!) growth as a blanket strategy.
His reasoning was that few and large increases were better than smaller and frequent increases.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 10:25 am
Robin Sasson (4/23/2012)
That's really interesting Jeff. Years ago I went on a MCP course for SQL 2000. The instructor recommended 50% (!!!!) growth as a blanket strategy.His reasoning was that few and large increases were better than smaller and frequent increases.
While what that instructor said about "few and large" compared to "smaller and frequent" is mostly true, there are points of diminishing returns. Using % for any type of growth except for a very narrow window in the size of a DB over it's life time usually isn't the way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 10:28 am
george sibbald (4/23/2012)
This is probably the link Jeff is thinking of.Check the URLs it links to as well.
With logs the important thing to be aware of the size and number of VLFS each chunk of growth will create.
Thanks for saving me the time. That's EXACTLY the link I was looking for.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 10:31 am
Thank you all....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply