January 23, 2007 at 9:30 am
One of our distributors has a msrepl_commands table with over 46 million rows and this is causing the distribution clean up job to run for a very long time and we are having problems replicating some large tables. We just set this up over the weekend so it's still a new installation.
i ran the system view on the distribution databases on both of our distributors and the indexes on them are extremely fragmented.
is it safe to run alter index on the distribution and msdb databases? I'm thinking this may speed things up
January 23, 2007 at 1:13 pm
I think you can do maintenance against this table...
msrepl_commands table is MS shipped but type = U which is user table...
Why you have millions of rows in msrepl_commands table...looks like cleanup agent is not working...
If your agent is not cleaning them you can delete manually...
You can run the following command it will delete all replicated commands but not non replicated...
EXEC dbo.sp_MSdistribution_cleanup @min_distretention =
0, @max_distretention = 0
MohammedU
Microsoft SQL Server MVP
January 24, 2007 at 6:41 am
the agent is working but it was taking over 6 hours to run. this distributor is replicating a few tables with close to 500 million rows between them.
January 25, 2007 at 8:15 am
I don't know about the distribution cleanup procedure, but the standard backup history deletion proc is such a dog that many people write their own version to eliminate the cursor. Of course the standard version is more robust, but it takes forever. You might take a look at what the distribution cleanup procedure does and try to think of a faster method that doesn't introduce too much risk.
It is possible to alter the indexes on the distribution tables, but I'm not sure if it is "safe". If there is that much replication activity then it will probably be a huge mistake to lock up the distribution tables with index rebuilds. You might get away with a DBCC INDEXDEFRAG which is not supposed to hold any long-term locks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply