reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JB
Shrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB
|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB
|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>
|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB
|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB