August 20, 2002 at 5:12 pm
I have a 3.2 Gig database, yet the overall size of the mdf file is 5 Gig. On the autogrow options, it's set to only grow an extra 10Mb at a time - so what happened?
Why does it constantly keep around 2Gig more in free space?
August 20, 2002 at 8:10 pm
There are several operations which could cause a temporary expansion in the database size. One that comes immediately to mind is the creation of clustered indexes. For it to have reached 5 GB means that at some point an operation pushed it out to that size.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 21, 2002 at 3:34 am
A common missed cause, at least for SQL 7, was maintainence of indexes. If you do a rebuild of indexes then the clustered index rebuild usually double the size of the database (excluding size of non-clustered indexes).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 21, 2002 at 6:06 pm
Therefore, I should run my optimisations job (which rebuild indexes) before the shrink database job? That would certainly explain why my database hasn't been shrinking.
August 21, 2002 at 6:53 pm
Thats it. Also good to remember that if you're tight on disk space have to be careful about optimizing, between growth in the mdf and the large number of transactions written to the log (depending on how much free space it has of course) you can hit the dreaded disk full error.
Andy
August 22, 2002 at 1:15 pm
In that case, the index build would fail and would essentially be rolled back. At least that's what happened in v6.5 (no autorgrow).
You should have a fair amount of free space. Running at the limit is bad for dbs as well as disks.
Steve Jones
August 23, 2002 at 3:37 am
I agree with Steve and in fact have seen this happen exactly as stated. If a process cannot complete it will roll back.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply