SEO

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

[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.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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.

Thanks Erland,

I did a DBCC DBREINDEX on every table that has an index, but still no
luck
Unused is about the same number and I cannot shrink the database and
further. I appreciate the info and if you have any ideas, please let
me know.

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.

Erland Sommarskog wrote:
>
> Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> > 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.
>
> Actually, wouldn't just adding a clustered index and then dropping it
> have the same effect as a DBCC DBREINDEX? <snip>

Yes it would. Of course, DBCC DBREINDEX on a table is less expensive
than adding a clustered index to a heap and then removing it again. The
DBCC DBREINDEX time is the same as the time it takes to add a clustered
index. However, the removal of the clustered index can take quite some
time, even more time than it takes to add the index.

So I agree with you that Brad should consider adding a permanent
clustered index.

Gert-Jan

You guys are great I'll try that thanks

If your ever in the Sonora California area I owe you a dinner and a beer.

Brad