re-index and database size

• crab7

  Mr or Mrs. 500

  Points: 548

  More actions

  July 10, 2003 at 8:51 am

  #63324

  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?

• bj007

  SSCommitted

  Points: 1928

  More actions

  July 10, 2003 at 11:25 am

  #464941

  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?

• crab7

  Mr or Mrs. 500

  Points: 548

  More actions

  July 10, 2003 at 1:52 pm

  #464972

  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.