July 10, 2003 at 8:51 am
Hi there, I just encoutered a, I think very weird, behavior of SQL server (2000, service pack 3).
I was doing re-index of some large fragmented tables of that database.
for example, before I did the re-index, I checked the unallocate space in the database called "prod" as follows:
sp_spaceused
5400.09 MB
the I checked the size of the table I was going to do re-index,
sp_spaceused IssueDeclaration
233408 KB
then I did re-index:
dbcc dbreindex (IssueDeclaration, '', 80)
after that, I re-checked the size of my database "prod", to my surprise the space of unallocated instead of increase, decreased!
sp_spaceused
5299.98 MB
which was exactly the opposite while I was doing the testing of re-index on another server ( I restored the dump of "prod" to a development server to estimate how long the re-index takes)
And after re-indexed several tables, I checked the "prod" again:
database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
PROD 47850.94 MB 3433.33 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
35396272 KB 18117544 KB 7952128 KB 9326600 KB
This is so weird, because the reservered space just increased and increased, then unused increased... while I was doing testing, actually the database shrink after index re-organized (both the index size and unused size decreased).
can anyone explain this?
July 10, 2003 at 11:25 am
Is it possible, the initial fillfactor was 90 and now you are doing it for 80 ?
Is the total size of db same between prod and devl?
July 10, 2003 at 1:52 pm
no, the table was pretty fragmented before re-index. And the db on testing was the same as the 'prod', because I used the dump file of 'prod' to build the test.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply