Sql Server huge tables with no rows

Question

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?

Answer 1

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.

Answer 2

Another way to deal with that kind of problem is to use the TRUNCATE TABLE statement:

TRUNCATE TABLE [lc_db_usr].[JMS_MESSAGES]

This will, however, only work on tables not referenced by a FOREIGN KEY constraint.

For more details: http://msdn.microsoft.com/en-us/library/ms177570.aspx

Answer 3

If the database contained many rows of data, then that data was deleted from the tables, the database engine will not shrink the files. There are commands to do this, I will have to look them up.

UPDATE: One I have used in the past is DBCC SHRINKDATABASE, you can find TechNet details here

Answer 4

I can't seem to post a screenshot, but what I do is right-click on the table, go to properties, and under "Storage":

Data space: 2,152.883 MB
FileGroup: PRIMARY
Index space: 0.063 MB
Partition scheme: 
Row count: 0
Table is partitioned: False
Text filegroup: PRIMARY

When I do SELECT * on thetable I get no results.

Here is the CREATE script (there is an image field there):

CREATE TABLE [lc_db_usr].[JMS_MESSAGES](
 [MESSAGEID] [int] NOT NULL,
 [DESTINATION] [varchar](150) NOT NULL,
 [TXID] [int] NULL,
 [TXOP] [char](1) NULL,
 [MESSAGEBLOB] [image] NULL,
PRIMARY KEY CLUSTERED 
(
 [MESSAGEID] ASC,
 [DESTINATION] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Answer 5

This is a bit weird but works:

Insert a dummy column as the first column of your table, then save the table.

Finally, delete the new column and save the table again.

Now you surely will have 0 bytes used.

Noel Paricollo

Answer 6

try this:

alter table table_name rebuild;