Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database

Running SQL Server 2012 standard, using both replication data and log shipping. Transaction log backups are created every few minutes.

This question is NOT about the amount of space reported by the file system.

We have a log table with four VARCHAR(max) columns. The table currently holds roughly 180 GB of data. The table grows at a rate of roughly 10 GB per month.

In an attempt to free up space for future growth, I recently deleted (in chunks over a 36 hour period) close to 30% of the data.

Based on the size of the table and the number of rows being removed, I expected the size of the table to be reduced by roughly 55-60 GB. I verified this by taking a sample and calculating the average row size, and then multiplying that by the number of rows to be removed. However, after finishing the delete, the table size has not changed.

While I am not looking to shrink the size of the .mdf file, I did check to see if the database had any free space that could be shrunk, but there is 0% free space.

So - I am confused. I've verified the data that I deleted from the table is no longer there. I even compared it against a backup to get a before & after. The number of rows in the database has changed, but the data space for the table has not decreased.

My questions then are:

1) Why did deleting data from the table not reduce the size of the table?

2) What can I do to actually reduce the size of the table?

EDIT:

• I am determining the space used by the table by calling sp_spaceused 'tablename'.

• I have used dbcc updateusage (0, tablename) prior to calling sp_spaceused.

• I am NOT trying to free up disk space - although an analysis indicates that there is 0% space available to be reclaimed.

• I have run an index reorganize with ( LOB_COMPACTION = ON ). Fragmentation is below 1%.

• The table is not a heap. Table definition below.

```
CREATE TABLE [dbo].LARGE_LOG_TABLE NOT NULL,
[DOC_ID] varchar NOT NULL,
[REQUEST_DATA] [varch

Your table has a lot of LOB data, and the only thing that can free up LOB pages is index reorg (with lob_compaction):

ALTER INDEX PK_LARGE_LOG_TABLE REORGANIZE WITH (LOB_COMPACTION = ON)

Index rebuild will not even touch LOB data, and backup/restore will bring your database to exact same state as it was at the backup time.

StackExchange Database Administrators Q#189966, answer score: 2