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