January 20, 2006 at 6:13 pm
Hi,
We have W2k and S2k. Yhe problem is a single file DB that still has significantly sized free space (per DBCC EXTENTINFO and lots o' analysis) suspended in many places within the file, yet sometimes the DB will cause NT extents to be taken for that file. What are the criteria for SQL to ask NT for an extent? I thought it would use up all available (and right-sized) space before expanding?
As a bonus question, the space available on the above volume can get low on its own and be less than the % we have specified for the DB file growth. The DB will just suck up whatever is left, regardless of size and not issue a warning (frankly, I'd rather have it fail because the full volume condition causes all sorts of other problems). Is this normal behavior?
Pointing me to suitable documentation for either would be most helpful and welcome. I have exausted my search on BOL, 2kRK, and several books. Most get close to the issue but don't cover what happens outside the DB.
Thanks,
Dave
January 22, 2006 at 9:05 am
A reason for sucking up additional nt extents instead of using the available place could be the original fillfactor of an index.
If you had an original table with an index place upon of fillfactor 50% (50% empty space reserved for new data) and you only add new items sequential, 80% of the empty place can be still be around because that index node at the start doesn't need to be adjusted.
Beware that sql server works with pages of 8k so there will "always" be some slack because of x records don't exactly match 8k.
Bonus question:
Specifiying incremens in % is risky business. 10% of 10 is 1, 10% of 10 million is still a million. Specify a maximum database limit and set your increments to a certain amount instead of %.
Is it a sql server a dedicated machine, is there an option to increase diskspace (quite cheap) or did you mean the log file keeps growing (transaction log backup solves this).
January 22, 2006 at 8:06 pm
Well, these are kind of related... Our problem is that we can see large, juicy, contiguous, unallocated chunks in the file (multi mb) from the EXTENTINFO command, yet the File still seems to expand. There has to be some logical, identifiable reason why SQL wouldn't use that space.
And that expansion is to the end of the volume and without failing, even though the NT available size isn't the full extent size (i.e. 10% current size). Shouldn't it just fail with some "no space for extent" message? Instead of picking some smaller amount (and how does it pick that amount?).
Can't seem to nail that process down in the doc or many novels written on the subject.
Thanks for the input
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply