Brad,

DBCC DBREINDEX only has effect on tables with a clustered index.

If your table does not have a clustered index, you can temporarily add
one, just for the purpose of DBCC DBREINDEX, or you can change one of
your existing indexes to be the clustered one.

Gert-Jan


Brad Tobin wrote:
>
> Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93C08F1434AEBYazorman@127.0.0.1>...
> > [posted and mailed, please reply in news]
> >
> > Brad Tobin (bradtobin@yahoo.com) writes:
> > > On a production database, there is a 2GB database, when I run
> > > sp_spaceused it indicates a very high quanity of unused space. The
> > > database has been shrunk & free space sent to the OS. Why is this
> > > value so high, what can I do to reclaim the space?
> > >
> > >
> > > database_name database_size unallocated space
> > > --------------------------------------------------------------------------
> > > DB_00001 2004.13 MB 49.64 MB
> > >
> > >
> > > reserved data index_size unused
> > > ------------------ ------------------ ------------------ --------------
> > > 1531248 KB 412720 KB 165168 KB 953360 KB
> >
> > Unused is not the same as free. Your unallocated space is 50 MB.
> > The unused space is reserved for existing tables to grow in. SQL Server
> > reserves space for tables in extents of 8 pages @ 8192 bytes. (The
> > first page for a table goes into a mixed extent though.) An extent
> > is not reclaimed, until all pages in the extent are unused.
> >
> > Your high level of unused, around 2/3 of what is reserved, indicates a
> > high level of fragmentation. A DBCC DBREINDEX should take care of that.
> > Not only will you get more disk space you can reclaim, but you can also
> > win quite some in performance.
> >
> > Note: if you expect that you will need the space again, shrinking the
> > data file is not a very good idea, as SQL Server will have to autogrow,
> > and this is a costly operation.
>
> Additionally when I do a DBCC DBREINDEX on the table, then use DBCC
> Showcontig the Scan Density has not changed, which indicates to be
> that the fragmentation still exists.