Shrink Db / Reindex issue

Shrink Db / Reindex issue

Post by Adam Yos » Sat, 22 Mar 2003 21:50:04



I used the DBCC Reindex command to reindex a table the
other day.  It took 8 hours to reindex that table with 30
million records and 19 indexes.  Each index has the
padding set to the default of 0.  Before the reindex, the
database was about 32 GB.  After the reindex, the database
was 72 GB with 42 GB listed as "available space".  As a
result, our 80 GB drive has only 8 GB free.

Therefore, I used the DBCC ShrinkDatabase ( "DBCC
ShrinkDatabase (DbName, 10)" ) to remove all that extra
space.  The "shrink" has been running for 24 hours now.  
That does not seem right, but I don't want to stop it in
case it would be done any minute.

I am just looking for ideas on if I did something wrong or
if there may be a better option for shrinking the Db.  Any
thoughts?

Thank you,
Adam Yost

 
 
 

Shrink Db / Reindex issue

Post by Tibor Karasz » Sat, 22 Mar 2003 22:02:55


Just to make it clear. Was it the data or the transaction log files that grew large?

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


Quote:> I used the DBCC Reindex command to reindex a table the
> other day.  It took 8 hours to reindex that table with 30
> million records and 19 indexes.  Each index has the
> padding set to the default of 0.  Before the reindex, the
> database was about 32 GB.  After the reindex, the database
> was 72 GB with 42 GB listed as "available space".  As a
> result, our 80 GB drive has only 8 GB free.

> Therefore, I used the DBCC ShrinkDatabase ( "DBCC
> ShrinkDatabase (DbName, 10)" ) to remove all that extra
> space.  The "shrink" has been running for 24 hours now.
> That does not seem right, but I don't want to stop it in
> case it would be done any minute.

> I am just looking for ideas on if I did something wrong or
> if there may be a better option for shrinking the Db.  Any
> thoughts?

> Thank you,
> Adam Yost


 
 
 

Shrink Db / Reindex issue

Post by Adam » Sat, 22 Mar 2003 22:24:58


It was the data file.

Quote:>-----Original Message-----
>Just to make it clear. Was it the data or the transaction

log files that grew large?

>--
>Tibor Karaszi, SQL Server MVP
>For help on TSQL, please provide code we can execute in
Query Analyzer



>> I used the DBCC Reindex command to reindex a table the
>> other day.  It took 8 hours to reindex that table with
30
>> million records and 19 indexes.  Each index has the
>> padding set to the default of 0.  Before the reindex,
the
>> database was about 32 GB.  After the reindex, the
database
>> was 72 GB with 42 GB listed as "available space".  As a
>> result, our 80 GB drive has only 8 GB free.

>> Therefore, I used the DBCC ShrinkDatabase ( "DBCC
>> ShrinkDatabase (DbName, 10)" ) to remove all that extra
>> space.  The "shrink" has been running for 24 hours now.
>> That does not seem right, but I don't want to stop it in
>> case it would be done any minute.

>> I am just looking for ideas on if I did something wrong
or
>> if there may be a better option for shrinking the Db.  
Any
>> thoughts?

>> Thank you,
>> Adam Yost

>.

 
 
 

Shrink Db / Reindex issue

Post by Tibor Karasz » Sat, 22 Mar 2003 22:37:00


Strange....

Thinking about it, SQL Server will allocate new storage for an index before it releases the old
storage. So you need space in the db. I would understand if you have one big table with a
clustered index (and perhaps a bunch of NC indexes). Still seems a bit high.

Also, I think that the shrink actually causes fragmentation (think pages shuffled here) so
jumping back and forth between extents might have become worse after the shrink. I assume that
SHOWCONTIG reports this type of fragmentation (but jumping pack and forth at row level is of
course much worse and that should not be affected by a shrink).

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


> It was the data file.

> >-----Original Message-----
> >Just to make it clear. Was it the data or the transaction
> log files that grew large?

> >--
> >Tibor Karaszi, SQL Server MVP
> >For help on TSQL, please provide code we can execute in
> Query Analyzer



> >> I used the DBCC Reindex command to reindex a table the
> >> other day.  It took 8 hours to reindex that table with
> 30
> >> million records and 19 indexes.  Each index has the
> >> padding set to the default of 0.  Before the reindex,
> the
> >> database was about 32 GB.  After the reindex, the
> database
> >> was 72 GB with 42 GB listed as "available space".  As a
> >> result, our 80 GB drive has only 8 GB free.

> >> Therefore, I used the DBCC ShrinkDatabase ( "DBCC
> >> ShrinkDatabase (DbName, 10)" ) to remove all that extra
> >> space.  The "shrink" has been running for 24 hours now.
> >> That does not seem right, but I don't want to stop it in
> >> case it would be done any minute.

> >> I am just looking for ideas on if I did something wrong
> or
> >> if there may be a better option for shrinking the Db.
> Any
> >> thoughts?

> >> Thank you,
> >> Adam Yost

> >.

 
 
 

Shrink Db / Reindex issue

Post by Andrew J. Kell » Sat, 22 Mar 2003 22:42:38


First off  it sounds like your disk configuration is not very proper for
those types of operations.  Sounds like you have a single array (probably
raid 5) and all your files are on the one array.   You might want to
consider adding another array for the logs at the least in the future.  19
indexes on one table?  I hope this is an OLAP and not an OLTP application.
The reason your db file grew so large is that (basically) SQL Server makes a
copy of your objects being reindexed and needs approx. 1.2 times the size of
the object free.  If not it will grow your db.  Now in doing this it places
that copy toward the end of your data file (that's why it grew).  So to
shrink the file, it must now move all that data closer to the beginning of
the file since it can only shrink from the end.  If your disk configuration
is not optimal this can take a long time.

--

Andrew J. Kelly
SQL Server MVP


> It was the data file.

> >-----Original Message-----
> >Just to make it clear. Was it the data or the transaction
> log files that grew large?

> >--
> >Tibor Karaszi, SQL Server MVP
> >For help on TSQL, please provide code we can execute in
> Query Analyzer



> >> I used the DBCC Reindex command to reindex a table the
> >> other day.  It took 8 hours to reindex that table with
> 30
> >> million records and 19 indexes.  Each index has the
> >> padding set to the default of 0.  Before the reindex,
> the
> >> database was about 32 GB.  After the reindex, the
> database
> >> was 72 GB with 42 GB listed as "available space".  As a
> >> result, our 80 GB drive has only 8 GB free.

> >> Therefore, I used the DBCC ShrinkDatabase ( "DBCC
> >> ShrinkDatabase (DbName, 10)" ) to remove all that extra
> >> space.  The "shrink" has been running for 24 hours now.
> >> That does not seem right, but I don't want to stop it in
> >> case it would be done any minute.

> >> I am just looking for ideas on if I did something wrong
> or
> >> if there may be a better option for shrinking the Db.
> Any
> >> thoughts?

> >> Thank you,
> >> Adam Yost

> >.

 
 
 

1. pgsql/contrib README reindex/README reindex/re ...

CVSROOT:        /cvsroot
Module name:    pgsql

Modified files:
        contrib        : README
Added files:
        contrib/reindex: README reindex

Log message:
        Add reindex utility to /contrib.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. I cannot add a connection into Connections collection, please help

3. pgsql/ ontrib/reindex/reindex oc/src/sgml/main ...

4. Sybase DBA Sought

5. Reindex entire db in one cmd

6. help with self join

7. When to reindex the DB files

8. Failure to connedct using Performance monitor

9. Reindex in suspect db

10. Shrinking database with low disk space - any issues

11. Keep T-Log from shrinking after issuing DBCC SHRINKFILE:

12. issue re: x-db ownership chaining and master db system tables

13. Shrink tables doesn't shrink enough