September 29, 2008 at 2:50 pm
On SQL, by default when we create a database the Autogrowth item is enabled with the option Unrestricted File Growth is selected.
To avoid in the future damages in the size disk what is the best pratice to configure this item?
Thanks for your answer.
Best Regards
September 29, 2008 at 3:10 pm
There won't be damages, you might run out of disk space, but then you just won't be able to insert/update data.
I set it to auto-grow, since if I have an emergency, I do want it to grow. I'd set it to a reasonable number, 10% might work, maybe 10GB works better, depends on your database and growth.
This does not mean you ignore space. You don't want growths to randomly occur. My best practice is to set enough free space to last you 3-6 months, then have a monthly reminder to check the space. I would look to grow it every few months so I always have free space in there and it grows in discrete amounts, not every month.
September 29, 2008 at 9:03 pm
Don't ever let the defaults for auto-growth on a database occur... it takes 73 disk fragments just to reach 1GB using the default settings.
I'm with Steve... make sure the size is set to accomodate at least 3 months of data and set the growth amount to somewhere between 250 and 500 mb so you don't parallize the whole system during an unexpected auto-growth. And, you should NEVER have an unexpected auto-growth. Never use % for auto-growth.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2008 at 6:11 am
On top of what these guys have very correctly told you, put a limit on the growth of the database so that it won't fill the drive. A full file or database won't affect the server. A full drive can.
"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
September 30, 2008 at 7:08 am
You should do regular database size checkups to see how often and how much your database grows on a weekly & monthly basis. Then configure your max database size (presuming your drives can handle the size) to give you at least 1 year's worth of growth. This will also help you come budget time because you can tell the boss "We're gonna need X more gigs of space on the SAN for our DB because it grows an average of Y amount every month."
And remember that shrinking your database can affect these numbers, so plan high rather than low.
September 30, 2008 at 7:27 am
The problem is that the log files increase a lot.
I have an database with 250MB and a Log File with 24GB.
After i reconfigure the Autogrowth i have to made a maitenance plan to automatically shrink the database?
Can i shrink manually?
Best Regards?
September 30, 2008 at 7:35 am
You can shrink manually, but it's not recommended. And if you're having to shrink the log files often, you don't have enough disk space and/or you have really terrible queries hitting your DB.
When files are shrunk, they have to grow again the next time queries are run. So essentially you're encouraging disk thrashing when you constantly shrink your files (log or data). Disk thrashing (or overly high I/O) is NOT a good thing.
Here's where monitoring your files comes in handy. You can use the info to prove to your boss how much more disk space you need for the DB. Also, you can use it as an excuse to enhance query performance. Also, you can write down how much time it's taking you to maintain this DB with all the shrink, etc. and figure out (Yearly Salary / weeks of the year / 40 hours = hourly salary) how much the company is paying you to fix the problem when they could spend this money on more space and leave you available for REAL work.
You shouldn't be having to shrink your log files that often and if you do manual shrinks, you should always do a checkpoint & a translog backup (if you're not in Simple recovery mode) BEFORE you shrink the log file. And never do it during work hours because that just gets messy.
September 30, 2008 at 8:14 am
Take a look at the following Microsoft kb article.
September 30, 2008 at 8:24 am
ruyfigueiredo (9/30/2008)
The problem is that the log files increase a lot.I have an database with 250MB and a Log File with 24GB.
After i reconfigure the Autogrowth i have to made a maitenance plan to automatically shrink the database?
Can i shrink manually?
Best Regards?
That sounds like you've got the database in full recovery mode and you're not backing up the log. You need to run log backups on a regular basis (start at once an hour) so that it doesn't just grow forever. It's pretty abnormal to have 250 [highlight=#ffff11]MB[/highlight] for a database and 24 [highlight=#ffff11]GB[/highlight] for a log.
"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
September 30, 2008 at 8:30 am
ruyfigueiredo (9/30/2008)
The problem is that the log files increase a lot.I have an database with 250MB and a Log File with 24GB.
After i reconfigure the Autogrowth i have to made a maitenance plan to automatically shrink the database?
Can i shrink manually?
Best Regards?
Are you doing a transaction log backup? The size of your log would kind of hint at the fact that you don't, and if you do - you're not doing it often enough. The last option I could think of would be some kind of over the top maintenance plan (like rebuilding every index every night), but even that would take a while to generate logs that big.
----------------------------------------------------------------------------------
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?
September 30, 2008 at 7:48 pm
Grant Fritchey (9/30/2008)
ruyfigueiredo (9/30/2008)
The problem is that the log files increase a lot.I have an database with 250MB and a Log File with 24GB.
After i reconfigure the Autogrowth i have to made a maitenance plan to automatically shrink the database?
Can i shrink manually?
Best Regards?
That sounds like you've got the database in full recovery mode and you're not backing up the log. You need to run log backups on a regular basis (start at once an hour) so that it doesn't just grow forever. It's pretty abnormal to have 250 [highlight=#ffff11]MB[/highlight] for a database and 24 [highlight=#ffff11]GB[/highlight] for a log.
We've got a couple like that... turns out that they have some insane indexes and the weekly DBCC IndexDefrag seems to be the reason for the log bloat. And, I really do mean some insane indexes... Table has 19 columns 9 of which are used in the clustered index and then they have a single column index on the other 10 which, of course, also uses the bloated clustered index. Since the clustered index is not in any type of chronological order, DBCC IndexDefrag has to work it's butt off. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2008 at 5:21 am
Just curious... How is performance (read/write) on those insane indexes of yours, Jeff?
October 1, 2008 at 5:22 am
I'm as curious as Brandie is and you'll have to agree, that's pretty abbie-someone.
"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
October 1, 2008 at 4:02 pm
also bear in mind regular autogrowths can leave you with physical file fragmentation 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 1, 2008 at 8:38 pm
Brandie Tarvin (10/1/2008)
Just curious... How is performance (read/write) on those insane indexes of yours, Jeff?
I don't know any gentle way to say it... Inserts, updates, and the occasional block delete for rework, which are the bread and butter of the system, suck. Selects run pretty well when properly formed... but there's not much proper form.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply