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?

Mike Gates
  • 185
  • 1
  • 1
  • 3
  • 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 01 '09 at 14:55
  • I answered this below – Mike Gates Dec 01 '09 at 15:07

6 Answers6

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.

BradC
  • 2,220
  • 4
  • 27
  • 36
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

  • 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 '20 at 19:07
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

0

try this:

alter table table_name rebuild;
chicks
  • 3,793
  • 10
  • 27
  • 36
eray
  • 1
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

Brettski
  • 942
  • 3
  • 20
  • 31
  • 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 01 '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 01 '09 at 15:04
  • Or do you need the data that may be contained the database files? – Brettski Dec 01 '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 01 '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 01 '09 at 16:18
  • So, where is this data stored so I can delete it? It this in another table? – Mike Gates Dec 01 '09 at 17:21
  • This is what DBCC SHRINKDATABASE /DBCC SHRINKFILE (better choice) are used for ([SHRINKFILE](http://msdn.microsoft.com/en-us/library/ms189493.aspx)). Question: Has this database been in use at all, or did you receive it like this? – Brettski Dec 01 '09 at 19:44
  • It has been in use quite a bit. I have tried both SHRINKDATABASE and SHRINKFILE...neither were successful. – Mike Gates Dec 01 '09 at 22:06
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]
Mike Gates
  • 185
  • 1
  • 1
  • 3
  • 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 01 '09 at 16:44