[posted and mailed, please reply in news]
Brad Tobin (br*******@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,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp