January 12, 2005 at 10:38 am
Ok before you say duh the t-log will grow, it isn't the t-log I am worried about it is the MDF. I have a database that was 86 gigs with 59 of that allocated to data, leaving 27 gigs free (per sp_spaceused) This unallocated space was not being used to database growth for some reason. To get rid of this unusable space I ran shrinkfile to get the size back under control. However when my nightly dbreindex jobs run the cause the 27 gigs off unallocated space to return which of course increase the total size of the DB. I know that dbcc dbreindex will cause the t-log to increase, but why is the MDF growing so much? Any help would be greatly appreciated as I am running out of options.
Thanks!
Jared
January 12, 2005 at 12:47 pm
I hit a situation similar to this a ways back--tried to reindex the database, and it triggered a database autogrow that failed due to lack of space. The exact details are fuzzy just now, but basically what's going on is that, when a table is being worked over, a second copy is being made (of it, the indexes, the clustered index... it'll say somewhere in BOL), and you need to store that in your database.
Essentially, SQL is making a sorted copy of table/index object, and only when that's done is the original dropped. So, for a brief stretch, you've got "double data", and odds are your database needs to grow to hold that much.
(I don't know but that "sortintempdb" might ease the pain...)
Philip
January 12, 2005 at 1:38 pm
From an article by Microsoft:
"All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows)."
Ryan
January 12, 2005 at 5:27 pm
Yeah, that was it on the nose. We ran out of disk space because of all the indexes on our largest data warehouse fact table.
Philip
January 13, 2005 at 2:43 am
That's why people should always add the projected size of their largest table to the projected database growth when looking at capacity planning for their systems.
We've been stung like this in the past at a former company and it's definately a good idea to be proactive with this sort of thing.
January 13, 2005 at 2:45 am
See, if this provides additional help:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply