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).