You can have empty space on the pages, needing more pages. "Reclaim" by rebuilding
indexes. (DBCC DBREINDEX)
You can have non-used pages in extents. Reclaim as above.
You can have non-used extents in the database. Reclaim by shrinking database file size
(DBCC SHRINKDB or DBCC SHRINKFILE).
Note that first two does not shrink file size. Read in books online about physical
database architecture to understand the page/extent concept.
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
> Have a SQL7 DB that uses 8GB...This number is based on tables and indexes
> tab in enterprise manager...Ran a DTS to copy the database...The copy is
> 4GB...Based on an anlysis of record count in tables the 4GB number makes
> sense...Three questions: Why is the original bigger than it needs to be?
> How can I get rif of the wasted space? Can someone point me to a discussion
> of the differences in storage requirements and performance between vchar and
> char data types.