December 12, 2007 at 10:46 am
Hi,
one of my database of size 1.5 GB ,with data and log file unrestricted growth of 10 MB but it always shows me 'space available' as only 6.4 MB even i ran dbcc updatestatus to cross check but it is same so is it something alarming so i can take action or is it impact the performance in any way?
December 12, 2007 at 12:01 pm
December 12, 2007 at 12:32 pm
Thanks Tommy ,i have already run that and the issue here is it actually it is showing correct around 6MB of free space , so is it a normal scenario for space available ,how the space available is calculated? is it related with my file growths?
December 12, 2007 at 12:39 pm
Until it becomes full, it will autogrow by 10%. In this case 10% of 1.5GB. I would avoid this if possible (hinders performance), forecast your space requirements and allocate the space to the MDF ahead of time so that the space is contiguous. You can always run a DBCC SHRINKFILE truncateonly if you don't use it.
Tommy
Follow @sqlscribeDecember 12, 2007 at 2:59 pm
What Tommy said, and my personal preference for growth is to used fixed size rather than percentage. Otherwise you get exponential growth. And if the database is going to grow, you can tell immediately how much disk space you need for the next growth unit (OK, so it might be a simple calculation otherwise, but a second saved is a second earned. Or something).
Not an issue if the database can be sized correctly from the start, of course.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 12, 2007 at 3:59 pm
Thanks ,so what you suggest ,it should be equivalent to per day database growth suppose 20 MB so let mark as 20MB growth and put some fixed size and remove un-restricted growth? or any other pointers to get better situation in terms of file growth settings?
December 12, 2007 at 4:28 pm
If you know the rough per-day growth rate, multiply it by the number of days you expect the database to be around for and set the database size to that (this is very basic, not counting things like data purging). If that is more disk space than you have available, set it so that database will only need to be grown (manually or automatically) once a year or so. The less times the database needs to grow the better. If it is (or will become) a large database, set a reminder to grow the database manually during a quiet period.
As far as setting a growth limit or not goes, there are divided opinions. If the data file is on a drive by itself, then not a lot of point setting a limit (unless you want to set an alert on % data file used - but that could be covered by a % disk space used alert). If it is sharing a drive (not recommended best practice, but common for smaller databases), then setting a size limit on the data file means that if it happens to hit that limit unexpectedly, it will only halt that database. With no limit, it could fill up the drive and halt ALL databases. That's my reasoning, anyway. Like I said, there are different opinions around, you need to decide what will work best for you.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 12, 2007 at 5:06 pm
Great ,also one more question ,i know dbcc updateusage correct page and row count inaccuracies in catalog views, as i had problem that my DB space available was showing to 0 and later it is corrected by running dbcc updateusage so while it was showing 0 at that time users will face performance issue or it is just to reflect ,and internally sql maintains the pages to grow, so in other words if i leave 'available space' as 0 and dont run dbcc updage usage how it is going to impact my sql server for new page allocation and is it going to impact the performance?
December 12, 2007 at 5:18 pm
Sorry, don't have an answer for that one. I'm not sure if SQL Server uses the views that DBCC updates or hits the tables themselves when it comes time to allocate more pages. The latter I suspect - the catalog views seem to be more for our use than SQL Server's.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 13, 2007 at 12:59 pm
My customer is coming up again , does any body know for sure?
Thanks....
December 13, 2007 at 2:02 pm
Yes... it does NOT affect performance of what SQL Server does for page allocation...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply