Before I complete my question let me provide context.

I’ve received an alert saying that a specific database could not allocate a new page (disk was full)

The message that you will see on the SQL Server Error log is:

I didn’t know the database structure or what is saved there, so I picked up a script from my toolbelt that shreds all indexes from all table. Just some information like number of rows and space that it is occupying. I have sorted by occupying space in descending order, look what I found…

zero_rows_with_occupying_space

So…my script has a bug? :-) No, it hasn’t!

The joy of heaps

The joy of heaps

First, the definition:

Source: MS Docs - Heaps (Tables without Clustered Indexes)

Until now, everything seems normal, it is just a table with unordered data.

Why am I talking about heaps?

Not because of table name (was created on purpose for this demo), let me show to you the whole row of the script:

heap_empty_occupyingspace_1

heap_empty_occupyingspace_2

Do you have a clue? Yup, index_id = 0. That means that our table does not have a clustered index defined and therefore it is an HEAP.

Even so, how it is possible? 0 rows but occupying several MB…

The answer is…on the documentation :-)

source: DELETE (Transact-SQL) - Locking behavior

That explains it!

So…what should I do in order to get my space back when deleting from a HEAP?

On the same documentation page we can read the following:

Following the documentation, it suggest we can to use the TABLOCK hint in order to release the empty pages when deleting the data. Example:

DELETE
  FROM dbo.Heap WITH (TABLOCK)

What if I didn’t that way or if anyone else run a DELETE without specify it?

You can rebuild your table using this syntax (since SQL Server 2008):

ALTER TABLE dbo.Heap REBUILD

This way, the table will release the empty pages and you will recovery the space to use on other objects in the database.

heap_after_rebuild

Wrap up

I hope that with this little post you understood how and why a HEAP can have few rows or even zero but still occupy lots of space. Also I have mentioned two ways to solve this problem. Also, I have found databases with dozens of HEAPS almost empty or even empty that were occupying more than 50% of the total space allocated to the database. And guess what? People where complaining about space.

To finish, I need to complete the title, Did you know…you should use TABLOCK hint when deleting data from a HEAP?

Thanks for reading!