Large table with no rows?

Question

I have a Sql Server 2005 database that contains many tables which are taking up a large amount of space (combined over 10 GB), and these tables have no rows in them. I can see the space and row count with right-click, properties (Data space and row count items).

Any ideas?

Answer 1

The transaction log and data file don't shrink when data is removed, the assumption being that you'll use it again. You can use DBCC SHRINKFILE and/or DBCC SHRINKDATABASE to reclaim the space if you actually need it.

Answer 2

Free space isn't removed from the database as records are deleted. Instead, it's left there and reused later as new records are added.

You can shrink a database manually, or set it to auto-shrink periodically.

Manually shrinking:

• With a keyboard: from a query window, use DBCC SHRINKDATABASE dbname.
• With a mouse: in Management Studio, right-click the database, and choose Tasks->Shrink->Database.

Auto shrink:

• With a keyboard: from a query window, ALTER DATABASE dbname SET AUTO_SHRINK ON.
• With a mouse: in Management Studio, right-click the database, choose Properties, choose Options, and set Auto Shrink to True.

A quick glance through the documentation only says that auto-shrinks occur "periodically." I didn't notice any mention of what that period is.

When you shrink a database, by default, only empty pages at the end of the file are removed. So if your database has a large number of empty pages followed by a single used page, it won't shrink much. When shrinking it via the GUI, there is an option in the shrink dialog to "Reorganize files before releasing unused space." Checking this option will cause the used pages to be moved to the front of the file before truncating the trailing empty space, but will also take longer.