August 30, 2010 at 9:33 am
I have a database that contains 46% unallocated space and I am unable to get the database to remain at a smaller size.
Fact about the database:
1. 50,566 MB disk space
2. 20,781 MB in unallocated space
3. Autogrowth by 1MB, unrestricted
Actions taken:
A. Reset INITIAL SIZE to 30,000 MB
B. Shrink DB to 30,000 MB, reorganizing pages before releasing unused space
With each of the actions above, the database will shrink to 30GB. Then it'll immeadiately start to grow again until it gets to around 50GB. It takes about 10 days to grow back to 50GB.
I have thought about turning off Autogrowth and just monitoring the free space. While this should fix the issue, it doesn't really solve the problem.
Any ideas as to why this is happening?
Rob
August 30, 2010 at 9:38 am
Do you have an auto rebuild indexes task running? That would explain it.
August 30, 2010 at 9:46 am
Markus (8/30/2010)
Do you have an auto rebuild indexes task running? That would explain it.
Yes. On Sundays, an SSIS package runs that checks DB integrity, rebuilds the indexes and updates statistics.
Should I turn off the index rebuild on that database? It only has four tables, two of which take up almost all of the space. One has 3.6 million records and only has the primary key defined. Te second has 48 million records and only has the primary key defined.
Rob
August 30, 2010 at 11:15 am
Should you shut it off, not if you want your indexes to be up to date. I would recommend leaving it alone and just understand that the DB physical file size is going to be larger than the actual used space. This is not uncommon or problematic.
So unless the DB is causing you problems with the amount of disk space it uses, I'd leave it as is. The only thing I'd change is the autogrowth setting. 1mb is not a good setting for this. I would set this to 100mb or 250mb personally.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply