May 6, 2010 at 3:52 am
can we use autogrow options in real time?i heard that it will effect the performance level ?is it true.
what is best method to troubleshoot the space related issues in real time?
May 6, 2010 at 3:54 am
Autogrow is realtime, but specify it to grow by MB and not %, and use realistic values - which only you will know after determining the growth over a week or so.
Also define the maximum size IF storage is an issue.
Disabling Autogrow may leave you with a full database 02:00 sunday morning 🙂
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
May 6, 2010 at 3:58 am
Not sure what you mean by "real time" in this context.
Allowing the database to autogrow regularly isn't generally a good idea because it affects performance and causes file fragmentation. It's much better to plan ahead and pre-allocate the amount of storage you think you'll need for at least several months in advance for example.
Enable autogrow as a fail-safe measure in case you run out of allocated space but make sure you monitor it. If the database does start to autogrow then take action by allocating more space so that it won't grow in future.
May 6, 2010 at 6:09 am
I'm in agreement with David & Henrico. The one point I would add, and it was touched on by Henrico, is that you really should put growth limits on the files, even though you enable auto-grow and, as David says, you allocate space in advance of when you need it. If you don't put limits on the files, they will fill the drive. It won't crash the server like in the old days, but it does make fixing the problem more difficult than it needs to be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply