Just like that puppy you got during the pandemic, your databases hunger for more. And they grow. And occasionally make a mess. I’ll stop the analogy.

Anyhow, it’s worthwhile to occasionally review the tables in a database to see which ones are growing every day, using the most space.

But what if during a review you see the largest table looks like this?

That’s around 24 GB of sweet drive space allocated for 0 records. But…how?

Let me show you how.

Fill me up to the top

Let’s look at an example. We’ll create a table, populate it with some data, then break out our trusty “sp_spaceused” to see how much space it uses.

CREATE TABLE SomeTable (
 SomeID int NOT NULL IDENTITY(1,1)
 , SomeData VARCHAR(1000)
 , SomeMoreData VARCHAR(1000)
 );
GO

SET NOCOUNT ON;

INSERT SomeTable (
 SomeData
 , SomeMoreData
 )
VALUES (
 REPLICATE('SOME*', 200)
 , REPLICATE('DATA!', 200)
 );
GO 1000

EXEC sp_spaceused 'SomeTable';

Execute this yourself and you’ll see this table is using around 2.8 MB of space.

Let’s DELETE that data and see what happens.

DELETE
FROM SomeTable
GO

EXEC sp_spaceused 'SomeTable';

Check it out. 0 rows, but still the same space used.

Not what you’re after. Now that you’ve seen how this can happen, let’s talk about why.

Don’t you stop ‘till I’m overflowing

The short answer is these things can happen when you use a heap, which is a table without a clustered index. But what exactly does that mean?

It means the data pages are in a totally random order, and the only thing connecting all the pages together are the Index Allocation Map (IAM) pages.

And the IAM pages in your heap only know which data pages are allocated, not the values contained in the data.

That’s why if we run a typical DELETE that locks data only at the level of the data pages, we end up with this phenomenon of 0 records consuming space. It’s explained in the DELETE documentation.

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

So, without a clustered index to account for any values in the table, the IAM pages still allocate the data pages and their space after a DELETE, even if you’ve deleted all the data on the pages.

That’s not what you need. So how do we fix this?

This is what I’ve waited for

As mentioned, the first thing you can do to resolve this behavior is to put a clustered index on the table, especially if you’re going to be doing lots of INSERTs and DELETEs. Since a clustered index sorts the rows of the table, it pays attention to those pages and deallocates them if they no longer have data.

If you want to keep the table a heap, you still have a few options to reduce the size. The are noted in the documentation I previously linked, but if you aren’t in the mood for clicking, here they are.

1. You could use a WITH (TABLOCK) hint in your DELETE statement. This causes the whole table to be locked, thus involving those IAM pages. But you may find there may still be some small amount of space still allocated using this method.
2. You could try ALTER TABLE <table name> REBUILD, but now you’re talking about executing an extra statement just to clean up your mess.
3. You could use TRUNCATE TABLE, because by design this command deallocates data pages, but that only helps if you’re removing all the data.

Just like you should think about why you want a puppy, you’re going to have to consider the intended usage of your tables. Microsoft has some good examples of why you would want a table to not have a clustered index in their Heap documentation, so take some of your time to examine a table’s expected pattern of usage if you happen to find any 0-record space invaders in the data part of your world.

Hope this helps you clean up the messes.