asked
VarBinary(Max) SQL Server
We have an odd situation happening with one of our tables in our database (sql server 2012 Sp1) and I am hoping someone can shed some light on it. This table has 3 columns id(bigint), timestamp(datetime),photo(varbinary(max). This table is part of transactional replication so it also exists in our history database (apart from operational). Both databases are on the same server. We have a nightly clean-up job that removes old data from both operational and history database. This table never has more than few thousand records (in operational database) during the day. Once the clean-up finishes and indexes are rebuild, the unused space from this never gets returned to OS. Next day when new data comes in, instead of using that unused space, table grows in size. When we look at the free space available on the data files we don't see anything available (not that we shrink our data files but just to give an idea). We have run cleantable command couple of times to reclaim this unused space but is this the best way to recover space? Our concern is that if we don't run cleantable command on some type of a schedule, no matter how much free space we have on the hard drive it will always fill up. Any thought or suggestions will be appreciated.
I think I have run into this before, or something very similar. Deleting the old records (a few million) each day in a table that had a few hundred million rows never released the space of the unused pages. A reorg wouldn't help, but the full `REBUILD` always did the trick. Are you sure that a REBUILD doesn't work? Can you update the Question with the output of `sp_spaceused` for that table _before_ you do a `REBUILD` and then immediately after?
My initial guess is that the cleantable command, while reclaiming the space, doesn't reduce the number of pages within the database itself so the size initially stays the same. The rebuild of the indexes not decreasing the size could be an issue of fill factor and/or page density. Due to the identity clustering key, any new inserts would logically go at the bottom of the clustered index thus creating new pages and therefore increasing the database size. It won't insert the record in the middle of the clustering key, so it won't re-use the unused space. I'm assuming of course that you are deleting data at somewhere in the middle of the table and not at the end. I tested this on my machine and that's what I found anyway. Steve Stedman has a good post on this:
http://stevestedman.com/2013/05/dbcc-cleantable/ I guess my question is how much space are you attempting to reclaim? Are we talking TB's worth of space? A few gigs?
The issue is that no matter how much space we have at our disposal, if space is held up by the table after deletes, the size of the database will keep increasing (in essence the disk space will increase). I feel the issue is with the ghost cleanup process on this database however my investigations doesn't show lots of records held up for ghost cleanup. I know that data pages are not compressed by default in a LOB fields but index reorganize and rebuild hasn't helped here. Any other suggestions?
19 People are following this question.
