I have a Sql Server database that has a few tables with zero row count but take up a combined 10 GB of space. I can see this by doing right-click/properties on the tables in question (data space is huge, between 1 and 6 GB, and row count is zero on these tables). I have no clue what could be causing this as I would assume zero rows would mean nearly zero space taken.
Any ideas?
Best Answer
Rebuild all indexes on the tables, including the clustered index. From Books Online:
Something like:
DBCC SHRINKDATABASE or (more preferred) DBCC SHRINKFILE will only do something if the space has actually been freed from the table itself. Also please make sure you are familiar with the issues associated with shrinking database files. The executive summary: NEVER use auto-shrink, only shrink files when necessary, and always follow up with a full reindex to defragment all the indexes you've just fragmented.