Large table with no rows?

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 ro开发者_运维技巧ws in them. I can see the space and row count with right-click, properties (Data space and row count items).

Any ideas?

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.

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.

By BradC at https://serverfault.com/questions/89814/sql-server-huge-tables-with-no-rows

Rebuild all indexes on the tables, including the clustered index. From Books Online:

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Something like:

ALTER INDEX ALL ON [lc_db_user].[JMS_MESSAGES] REBUILD

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.