February 20, 2003 at 7:37 am
Hello,
I deleted 500,000 rows from a table. Ran DBCC CHECKTABLE and saw that the table was taking up 3790 pages. I then ran DBCC DBREINDEX then DBCC CHECKTABLE after that, and found that the table was now taking up 4096 pages.
I thought that the number of pages would decrease due to the 500,000 rows being deleted. Can someone explain why the table is now taking being stored across more pages?
Thank you.
Melanie
February 20, 2003 at 12:00 pm
What is the fill factor setting for this table? DBCC DBREINDEX will reorganize the table according to the fill factor and that could be the reason.
February 20, 2003 at 12:50 pm
Hello,
The fill factor is 80%.
Can you please explain how this could be the cause? I guess I'm confused because I thought that with deleting half a million records, the number of pages the index takes up would be less after being reindexed.
Thanks!
Melanie
February 20, 2003 at 1:04 pm
Maybe before the delete you had very little free space in the pages, reindexing spread the data out to take up more pages than before. Not sure that accounts for all of it though.
Andy
February 20, 2003 at 1:17 pm
Hi Andy,
Great! That makes some sense. Thanks for clarifying.
Melanie
February 20, 2003 at 3:33 pm
Did you have any text fields in the tables. I believe Andy is right but am curious about the later as text does not compress if there is free space and will use it back up. I fought an 18GB DB where that turned out to be the case.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply