Auto grow option

Question

Auto grow option

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
November 3, 2008 at 2:17 pm

#128234

Should be the autogrow option enabled or disabled? Currently, the db I am dealing with is 75 GB and the autogrow option is enabled with 10% restricted growth. Should I change the option to disable with fixed growth? How do you change the option with fixed say 120 GB option? Please advise. 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

SanjayAttray SSChampion Points: 13157 More actions · Answer 1

hydbadrose (11/3/2008)
Should be the autogrow option enabled or disabled? Currently, the db I am dealing with is 75 GB and the autogrow option is enabled with 10% restricted growth. Should I change the option to disable with fixed growth? How do you change the option with fixed say 120 GB option? Please advise. Thanks in advance.

If file size is 75 GB then instead of 10% growth I would prefer to grow file by 500 MB or something less figure like that. This way you are controlling the growth size of database and file.

select database. right click properties -> files -> then in autogrowth you can change type or else

USE [master]

GO

ALTER DATABASE [your database name] MODIFY FILE ( NAME = N'Databasefile_data', FILEGROWTH = 512000KB )

GO

SQL DBA.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I would usually advise to leave autogrow on. Not leaving auto-grow on means your database runs the risk of running out of space, in which case everything comes to a grinding halt.

That being said - unless you enable instant file initialization, auto-growth events can take time, and tend to interfere with performance, so you should make an effort to never allow auto-grow events to happen. Keep an eye on your free space, and when it gets too low, pick your slow time and grow the file yourself. Grow it "big" so that you don't have to continuously keep growing the file. A lot here will say - plan on enough free space so you don't have to do it again for months or even years.

Finally - remember that you don't want an overly small growth factor since it will lead to heavy disk fragmentation (which will then lead to lost performance all on its own). So - depending on how big and how fast your DB might grow - you might consider a fixed growth factor of 250MB or higher (although I probably wouldn't go much higher than 1GB so as to not take too much time growing the file).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Donn Polix Default port Points: 1453 More actions · Answer 3

I'd put in a 5 GB increment for a 75 GB data size. Small increments would lead to frequent growths. Pre-allocate the data with the right size, depending on the rate your data file grows.

Enabling autogrow is ideal in most occassions, but there are situations where you wanna limit the size of a particular database and turn autogrow off and not take up a significant amount of the drive space when it is sharing the same drive with a more critical database. Here, you have some sort of a purging job at least to handle the amount of data to a minimum.

_____________
Donn Policarpio

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

Matt Miller (11/3/2008)
I would usually advise to leave autogrow on. Not leaving auto-grow on means your database runs the risk of running out of space, in which case everything comes to a grinding halt.
That being said - unless you enable instant file initialization, auto-growth events can take time, and tend to interfere with performance, so you should make an effort to never allow auto-grow events to happen. Keep an eye on your free space, and when it gets too low, pick your slow time and grow the file yourself. Grow it "big" so that you don't have to continuously keep growing the file. A lot here will say - plan on enough free space so you don't have to do it again for months or even years.
Finally - remember that you don't want an overly small growth factor since it will lead to heavy disk fragmentation (which will then lead to lost performance all on its own). So - depending on how big and how fast your DB might grow - you might consider a fixed growth factor of 250MB or higher (although I probably wouldn't go much higher than 1GB so as to not take too much time growing the file).

I agree with Matt... auto-growth should never take you by surprise but, if it does, I sure wouldn't have it grow by 5 gig. Short spurts of 250MB to 500MB will allow the DB to grow without the growth causing a mini "outage" while it grows.

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I am with Matt on this one. I have databases that range from 50MB up to over 300GB, and all of them have auto grow turned on, though the size is set based on the database size. However, I monitor all of my databases with a automated script that emails me as soon as the files get to 10% free space. This generally means that there is enough free space to get through the day and allow me to grow it in a scripted task at night.

You can find the code that I use to do this on the following link:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 6

Thank you very much guys- I really admired all of your comments and suggessions.

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 7

my manager is adamant to setting up a size for the datafile.

Can anyone please send me the script to set up the size for data file. He wants the auto grow to be disabled.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

It's all in a single ALTER DATABASE statement. Look at this one (since you'd be changing the growth settings for each of the files involved)

http://msdn.microsoft.com/en-us/library/bb522469.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

I would make sure I had it in writing that they understand that the if the database runs out of space with a fixed size, and auto-grow disabled, faster than you can react, that the application will experience problems trying to create or change data in the database. This would not be acceptable at my company, but my manager also doesn't dictate how I configure the databases in SQL either.