October 6, 2009 at 9:55 am
I have a database that never really gets above 4 gigs in size. I've alotted 10 gigs of space to this database. What I am trying to accomplish is preventing the database from autogrowing when it seemingly does not need to.
The nature of the data in this database follows the following cycle:
1) Bulk loads into staging tables
2) Aggregating staging data into holding tables
3) Truncation of staging tables
This cycle runs every few minutes. The bulk load takes up about 90% of the physcial storage and the holding tables take up about 10%. So if I import 100 megs of data - I'll end building about 10 megs of data into the holding tables before deleting the 100 megs of staging tables. So there is A LOT of data in / data out going on.
I have been noticing that the database is autogrowing slightly every day. I also notice that the database is reporting 0 megs of available space when I check up on it. At this point I run a DBCC updateusage on the database and it then reports 6-9 gigs of free space at any given time.
Does anyone have any thoughts on what is actually happening behind the scenes with this command? Should I be running an updateusage at the end of every cycle? Should I just schedule updateusage to run every 4 hours?
If anyone has run into a similar situation I'd love to hear about the solution you put in place to prevent the unneeded autogrowing.
October 7, 2009 at 6:16 pm
SQL elder then 2005 have some problems with updating some system tables. Because dbcc updateusage is generating some stress you shouldnt run this after every load cycle. But before every db size check this commad should be perform.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply