|
Home > Archive > microsoft.public.sqlserver.server > December 2002 > DBCC DBREINDEX Question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DBCC DBREINDEX Question
|
|
| Wayne Antinore 2002-12-19, 9:24 am |
| Hi,
I have a question about DBCC DBREINDEX. When it rebuilds the indexes does
it really make a new index and then drop the old one (this would make
rollback for any errors easy)? The reason I ask this is that as part of our
product upgrade I rebuild the indexes on the database. In this case the
database is MSDE 2000 which has a 2GB size limit on the database. The
database starts out at 1.25 GB. There is one fairly heavily indexed table
that contains about 3.3 million rows (that are long as well) that makes up
the bulk of the space used. Right now it looks like when the reindex takes
place the database quickly grows to over 2GB and then I get the primary file
group full error due to the 2GB limit. Perhaps explicitly dropping and
recreating the indexes is the way to go? DBCC DBREINDEX was nice because it
was a single SQL Statement. Now I have to worry about keeping the DDL
around to rebuild the indexes. Thanks in advance for any help.
Wayne Antinore
| |
| Kelly Rowe 2002-12-19, 11:23 am |
| I am guessing that you are rebuilding a clustered index. The answer then
is, yes. Rebuilding a clustered index on a table requires enough space for
a complete second copy of the data, and the clustered index pages. A good
rule of thumb is that you will require at least 125% of your largest table
size available as free space in the database to rebuild a clustered index.
Kelly
"Wayne Antinore" <wantinore@veramark.com> wrote in message
news:OqZvZA3pCHA.1988@TK2MSFTNGP09...
> Hi,
> I have a question about DBCC DBREINDEX. When it rebuilds the indexes does
> it really make a new index and then drop the old one (this would make
> rollback for any errors easy)? The reason I ask this is that as part of
our
> product upgrade I rebuild the indexes on the database. In this case the
> database is MSDE 2000 which has a 2GB size limit on the database. The
> database starts out at 1.25 GB. There is one fairly heavily indexed table
> that contains about 3.3 million rows (that are long as well) that makes up
> the bulk of the space used. Right now it looks like when the reindex
takes
> place the database quickly grows to over 2GB and then I get the primary
file
> group full error due to the 2GB limit. Perhaps explicitly dropping and
> recreating the indexes is the way to go? DBCC DBREINDEX was nice because
it
> was a single SQL Statement. Now I have to worry about keeping the DDL
> around to rebuild the indexes. Thanks in advance for any help.
>
> Wayne Antinore
>
>
| |
| Wayne Antinore 2002-12-20, 7:23 am |
| Hi Kelly,
Thanks for getting back to me. Yes it is a clustered index. I thought that
is what was happening but it is good to have confirmation. Instead of the
DBCC DBREINDEX I'm BCPing the data out and back in. It's much faster than
the reindex was so this works as a better solution.
Thanks again
Wayne
"Kelly Rowe" <kelly.rowe@starwoodvo.com> wrote in message
news:#nKUI43pCHA.988@TK2MSFTNGP09...
> I am guessing that you are rebuilding a clustered index. The answer then
> is, yes. Rebuilding a clustered index on a table requires enough space
for
> a complete second copy of the data, and the clustered index pages. A good
> rule of thumb is that you will require at least 125% of your largest table
> size available as free space in the database to rebuild a clustered index.
>
> Kelly
>
> "Wayne Antinore" <wantinore@veramark.com> wrote in message
> news:OqZvZA3pCHA.1988@TK2MSFTNGP09...
> > Hi,
> > I have a question about DBCC DBREINDEX. When it rebuilds the indexes
does
> > it really make a new index and then drop the old one (this would make
> > rollback for any errors easy)? The reason I ask this is that as part of
> our
> > product upgrade I rebuild the indexes on the database. In this case the
> > database is MSDE 2000 which has a 2GB size limit on the database. The
> > database starts out at 1.25 GB. There is one fairly heavily indexed
table
> > that contains about 3.3 million rows (that are long as well) that makes
up
> > the bulk of the space used. Right now it looks like when the reindex
> takes
> > place the database quickly grows to over 2GB and then I get the primary
> file
> > group full error due to the 2GB limit. Perhaps explicitly dropping and
> > recreating the indexes is the way to go? DBCC DBREINDEX was nice
because
> it
> > was a single SQL Statement. Now I have to worry about keeping the DDL
> > around to rebuild the indexes. Thanks in advance for any help.
> >
> > Wayne Antinore
> >
> >
>
>
|
|
|
|
|