11 martie 2012 04:39
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
12 martie 2012 15:49ModeratorYou 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
12 martie 2012 16:46
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?
- Marcat ca răspuns de Maggie LuoMicrosoft Contingent Staff, Moderator 5 aprilie 2012 03:09
6 aprilie 2012 17:07Fixed 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.