I have a production MS SQL Server 2005 DB that I have recently become an "unintentional DB Admin" for... while poking around the settings for the DB I found that it has the default AutoGrow settings of 1MB. I've read (and generally believe) that this is not an appropriate setting - the DB is 14GB large. I'm willing (and likely) to change it, but my question is... If no one noticed it in all of the years we've been using it (especially users), why should I change it now? To put it another way, I guess I understand on a basic level why 1MB is too small - it's probably autogrowing often, perhaps daily or several times a day - but I'm thinking "if it ain't broke, don't fix it".

What specific justifications are there to increase the autogrow? Does it lock users out while autogrowing? Does it re-index? Does the DB do anything besides just add more disk space to the DB?

  • I wouldn't exactly subscribe to the ain't broke don't fix it motto in IT. A lot of things can be done badly and still "work". You were right to look into this as a lot of the time people ignore all of these little problems until they end up with a giant mess. – Sean Howat Jul 28 '10 at 14:10

Here's a great MS KB article on the topic, which covers all of the potential performance issues: Considerations for the "autogrow" and "autoshrink" settings in SQL Server

I've never seen the default value being 1 MB, though. I believe that the default setting is actually 10%. [Edited per comment by GregD]

| improve this answer | |
  • 1
    Default value for the transaction logs is 10% and the data file is 1MB. – GregD Oct 12 '09 at 15:08

That's not really what autogrowth is for. Autogrowth is a safety valve, so that the database doesn't explode when some unusual growth happens. You'd be better off running a nightly maintenance job to determine if the database will need to grow during that day, and resizing if necessary.

The biggest headache I see is with transaction logs; if you have a big transaction, and it outstrips the size of the transaction log for your database, it's going to delay the heck out of your transaction. Likewise if you're doing a massive insert or something, it's going to slow that down dramatically as well (because it's growing the database as it goes along).

| improve this answer | |

It's really hard to answer this question because the answers will be as varied as the people here and we have no idea what your current performance is now.

How is your performance currently?

Autogrowth can impact your users. Frequent autogrowth can frequently impact your users.

How big are your current transaction logs?

Depending on how big your transaction logs are, setting the autogrowth size as a percentage, instead of a fixed growth, can impact your users.

Autogrowth is not a replacement for proper capacity planning.

| improve this answer | |

I have heard that when you autogrow in small portions (1 Mb) that the database becomes fragmented which will adversely affect the performance.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.