April 13, 2011 at 1:08 pm
I got an alert about one of our databses mdf file is 96% full.
The database is setup to auto grow, 10%.
What should I do, shall I just wait for the database grow by itself eventually ? It won't give error, is that correct?
April 13, 2011 at 1:11 pm
Actually I tried to change in the database property -initial size area to a bigger size. But then I realize it is too big, now I have 50% free space, can I change again to a small number?
Thanks
April 13, 2011 at 10:33 pm
HI,
1) Its always recommend to have data file growth in MB than in %.
Reason: If data file is huge say 250 GB then 10% means 25 GB growth, so os has allocated 25 GB which will affect your sql server performance surely.
2) Always have some free space in data file to avoid frequent data file growth to gain good performance.
Remember to keep extra space in the data file to manage index rebuilds and normal growth
April 13, 2011 at 10:47 pm
50% free space may or may not be too much. It really depends on the size of the database and the amount of new data added daily.
I would leave the extra space in the file if you have adequate disk space to support it. Having that free space in the data file will help to prevent an autogrow of the database at the wrong time (for instance during a high transaction high volume part of the day when every resource is critical). It is good to have enough free space in the data file to accommodate up to 3yrs (again it depends) of new data being added to the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2011 at 7:23 am
Just remember that auto-growth is a safety valve, not a management plan. You should set the database to be bigger than it needs to be and then monitor the space and grow it yourself in large, managed, chunks, instead of leaving it on auto grow and just letting whatever happens, happen. You'll have much more consistent behavior (growing the file uses system resources and can lead to blocking), and less fragmentation if you manage the space.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply