5

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?

  • 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.)? – boydc7 Dec 1 '09 at 14:55
  • I answered this below – Mike Gates Dec 1 '09 at 15:07
4

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.

| improve this answer | |
  • 1
    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
1

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

| improve this answer | |
  • I am not sure exactly why in all these years I only run into this problem on MS SQL Server 2016. In my specific case, the application uses a table prefixed by tmp_, to copy records into, and run reports from, as an efficiency thing. This particular case being a general ledger table, which has hundreds of thousands of rows per month generated. I observed the same exact situation, 0 records in the table, but 4GB+ of Data Space used - and additional rows cannot be inserted - because the primary database file is now also full. Running the Truncate Table statement as suggest by DerOlli worked for m – M Ramazoodle May 25 at 19:07
0

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

| 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
0

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]
| 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
0

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

| improve this answer | |
0

try this:

alter table table_name rebuild;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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