Table Space not being freed up after large DELETEs [closed]

Question

We offload execution log data from SSRS into our main database for auditing purposes. We keep a year's worth of this data "online" in our main db and have a weekly job that offloads anything over a year to a separate archive db.

In our main db, a year's worth of data is equal to roughly 9 million rows, so in this table there is consistently about 9 million rows. Each week we import about 150K new rows, and delete about 150K old rows.

Over time, we see that the number of rows stays pretty consistent, but as of April 1, the table size has started growing (to 5 times it's original size since April 1). On April 1 we added an NVARCHAR(MAX) column to the table.

It would appear that as a result of adding the NVARCHAR(MAX) to the table, the "garbage collection" is never freeing up the unused space in this table when we delete the 150K rows each week.

The table has a clustered index (IDENTITY PRIMARY KEY).

What can I do to free up this space?

The documentation for DBCC CLEANTABLE discusses freeing up space after a column is dropped. Not sure that applies in my situation.

(I have no interest in shrinking my DB or data files - that has nothing to do with my issue - rather it is the allocation of table space that I am trying to free up so I end up with a lot more free space within this physical database file).

Answer 1

There is a memory leak when dealing with heaps (no clustered index). Multiple deletes/updates causes space to be "lost". It can easily be fixed by either adding a clustered index or rebuilding the table.

ALTER TABLE tablename REBUILD

However in this case the table is stated as having a clustered index and the same problems don't apply. Using Occam's Razor I'm going to guess that this is because of the varchar(max) column that was added. Remember that when you add a varchar column very little space is added to each row until data is actually added to the column. So in this case my guess is that as the varchar(max) column has been filled in the table size has grown.

Also remember that this table is a running year. That means that when the column was added presumably it wasn't filled in for any of the old rows. As the year goes by and old records are dropped off and new records come in a higher and higher percentage of the rows will have that column filled. Not to mention the possibility of additional information being added over time. (New comments, additional requirements when putting data in the column, whatever). This means that the table will grow over time, but should stabilize somewhat.

A very easy way to check the size of a table is to use the system stored procedure sp_spaceused.

 -- Using msdb.dbo.backupset as an example
 exec sp_spaceused 'backupset' 

name        rows        reserved    data    index_size  unused
backupset   14793       13072 KB    9112 KB 1640 KB     2320 KB

Note how we are given the number of rows, the total size of the table, how much is used for data and how much for the index. And in this particular case most importantly how much is unused.

Every table is going to have some unused space, but if you start seeing that number climb then that would be a warning sign. I've never seen it happen on a non-heap but of course I haven't seen everything either.

Answer 2

I've dealt with this for years. Deleted data from LOB data types isn't always cleaned up. SQL2008 and earlier never reclaimed the space in my experience. Was supposed to be fixed in SQL2008 R2, but I found it was partially fixed. Two fixes for it; 1 - Defrag using 'reorganize' (Rebuild isn't suppose to reclaim the space), or perform a database shrink.

Preferred command: alter index all on table_name_here reorganize