Cause for Large size (mb) table with few rows. merepl_Commands.

Question

I've had a distribution database growing for over a month now.  It will not reduce it's size regardless of row count. 

We have CDC replication using a dedicated ETL server which deletes the records out of distribution after they've been written, so the msrepl_commands always remains with very few rows.

Our Distribution Cleanup Job used to run twice a day (which was never even needed).  I've since shut the job down because it causes the volume to run out of space (which is currently set to 100 gb)

Distribution seems to be growing 7-8 gigs a day with the same row counts throughout the day.  The available space is always 1 mb or less.  Why would a table/database not release unused space that wasn't even allocated? Why does sp_spaceused on msrepl_commands look like this? (it's usually only a couple hundred rows, but there happened to be an update occurring when I posted this.)

name                         rows            reserved            data                 index_size    unused
MSrepl_commands    36078          76889560 KB    76173512 KB    705584 KB    10464 KB

Answer 1

You may want to try a reindex to consolidate space.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Thanks for the response.  I think that will be the first thing I will do.  I actually found a very similar post (below), which has pointed me in the right direction.  Now, I need to find a way to see what those version_ghost_records actually are. 

Hopefully it's only a few articles so I know what CDC job to look at.  Is there a way to see the actual data of these version_ghost_records?

http://social.msdn.microsoft.com/Forums/zh/sqldatabaseengine/thread/39b1662a-f7ca-4c8f-8a3f-78c376a075f3

Answer 3

Fixed the problem.  We had the distribution agent using the Verbose history agent profile.  Changing back to the default profile changed the HistoryVerboseLevel to 1, which now allows the version ghost records to be cleared.