Autgrouth value for user and tempDB Databases

Question

Post reply

Autgrouth value for user and tempDB Databases

krishnaprasad14

SSCrazy

Points: 2903
More actions
April 23, 2012 at 7:45 am

#392086

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

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:

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 2

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......

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 3

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:

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 5

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:

george sibbald SSC Guru Points: 104210 More actions · Answer 6

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.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 9

krishnaprasad14

SSCrazy

Points: 2903

April 23, 2012 at 10:31 am

#1477631

Thank you all....