Autogrowth for data file

Question

Autogrowth for data file

WhiteLotus

SSChampion

Points: 12122
More actions
January 15, 2020 at 12:19 am

#3715467

Hi All
I am looking at the recommendation of Data file autogrowth per database . The autogrowth will be based on MB instead of % . To get the autogrowth number , I have the script to check the Average daily growth per database and will multiply the number with 7 ( as I want to see grow every 7 days ).
The thing is some databases has zero growth or even minus due to shrinking process
How do deal with this ?
I mean if it has zero growth , how do I set the autogrowth number ?
Thank you
Your feedback is much appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

If it has zero or negative growth, why are you worried about the precise value of an autogrowth number? Pick a number (for example, 50 or 100MB) and move on.

I also think you may be spending a bit too much concern about autogrowth. While I agree that setting it way to small or way too big can be a bad thing, autogrowth of MDF/NDF files isn't a performance concern if you have "Instant File Initialization" enabled. If you're not allowed to have it enabled then, yeah, planned growth can be a winning move.

Autogrowth of LDF files is a whole 'nuther story because "Instant File Initialization" doesn't help there so much. Fortunately, they shouldn't need to grow often.

Setting both to some value in MB rather that % is definitely a winning move, though.

Shifting gears a bit, what you should really do is be very concerned about your "shrinking process". That's normally one of the worst things in the world you can do to a perfectly good MDF/NDF file and is a total waste of time for LDFs unless you first figure out what is causing undesirable log file growth and fixing it. Then, you only need to do a 1 time shrink.

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

Michael L John One Orange Chip Points: 27060 More actions · Answer 2

WhiteLotus wrote:

The thing is some databases has zero growth or even minus due to shrinking process

Please describe the "shrinking process". And why there is one in place.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

WhiteLotus SSChampion Points: 12122 More actions · Answer 3

Thanks for the response Guys . Much appreciated

Quoting from Jeff Moden,

"Shifting gears a bit, what you should really do is be very concerned about your "shrinking process". That's normally one of the worst things in the world you can do to a perfectly good MDF/NDF file and is a total waste of time for LDFs unless you first figure out what is causing undesirable log file growth and fixing it. Then, you only need to do a 1 time shrink."

Response : We only shrink if it is really needed - for emergency case ( Auto shrink is disable )

"Setting both to some value in MB rather that % is definitely a winning move, though."

Question : Do you mean setting same value (in MB) for LDF and MDF/NDF ?

Why ?

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

No. I'm NOT recommending that you set a MB value the same as the MDF/NDF for LDF although they can be set the same. "It Depends". On what?

The LDF file is actually a formatted file... kind of like a hard-disk is formatted. When an LDF file is first built or grows, if formats the file with VLFs (Virtual Log Files). The number of VLFs that the new space will be formatted with is a bit of a science. You have have way too many (which really slows down restores from backups and other things) or way too few (which makes logfile reuse more difficult, among other things).

Google for "sqlskills vlf" for a bunch of excellent articles on the subject remembering that some of it has changed over time and that's why they have a bunch of edits and links to new articles in some of the older articles.

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

WhiteLotus SSChampion Points: 12122 More actions · Answer 5

WhiteLotus

SSChampion

Points: 12122

January 17, 2020 at 3:41 am

#3716188

Thanks Jeff

Sure will look for it