Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Can you post the table schema and a screen shot/resultset from a query that shows your table sizes? Any chance the tables have BLOB/CLOB columns (i.e. text, image, max types, etc.)? –  chadhoc Dec 1 '09 at 14:55
    
I answered this below –  Mike Gates Dec 1 '09 at 15:07

5 Answers 5

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.

share|improve this answer
    
This did not fix the problem except for clearing up a few MB held by the indexes. –  Mike Gates Dec 1 '09 at 17:23

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

share|improve this answer
    
The strange thing is that there is no free space in the database (well, a little bit but not much). So shrinking should does not help this situation. –  Mike Gates Dec 1 '09 at 14:36
    
Well if there are no rows int the tables, truncate the files, you don't need any of the data that may be lost in there anyway. –  Brettski Dec 1 '09 at 15:04
    
Or do you need the data that may be contained the database files? –  Brettski Dec 1 '09 at 15:04
    
Well it seems like there is data, I just don't know how to get to it. This is a database from a 3rd party vendor which doesn't have a clue what is wrong (but they are pretty bad with support). –  Mike Gates Dec 1 '09 at 15:09
    
Seeing that you have that blob field in the database: [MESSAGEBLOB] [image] NULL, It may be that there where records their at one time and deleted. With blobs the row only holds a pointer to another place in the file where the blob is stored. The space used by the blob may not of been released when the rows where deleted. –  Brettski Dec 1 '09 at 16:18

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]
share|improve this answer
1  
Thanks for the detail, Mike. FYI, its preferred to include this kind of follow-up info as an edit to the question, instead of as its own answer, which can get lost in the shuffle of other answers. Welcome to ServerFault! –  BradC Dec 1 '09 at 16:44
    
I see...thanks! –  Mike Gates Dec 1 '09 at 17:21

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.