Cleaning large tabel MSdistribution_history and MSrepl_commands

Hy
I noticed that my transactional repl is working slower when on the other side MSdistribution_history and MSrepl_commands tabel geting biger and biger. Replications still works OK, but anyway slower. Cleaning agents work OK, there are no errors, so I dont
understand why MSdistribution_history tabel is so big.I would undersand that if there were some undelivered transactions, but everything looks fine.
Is there anyway I can delete some data in MSdistribution_history (600Mb) and MSrepl_commands (200Mb )to decrease tabel size and gain performance to speed up replication like it work 2 months ago?
Frank,
You must rely on the distribution cleanup agent to remove these records,
although the default schedule is every 10 mins so that is not likely to be
the issue here.
Do you have anonymous subscriptions. If so, then the transactions will hang
around until the timeout (3 days default). Also the same applies if you have
a subscriber who doesn't synchronize often which is worth checking.
You can also investigate this using sp_browsereplcmds to have a look at the
commands in readable format to check against the data on the subscribers.
HTH,
Paul Ibison
|||Hi Paul
I don`t have anonymous subsribtions and there is no subscriber which has not recently synchronized with publisher.
I also noticed, that anythinks I do on the distribution database (querying etc) takes a lot of time. I am running maintainance plan periodicly, I dont see any problems with database.
sp_browsereplcmds returned 725005 records and I see "data" for all artices (8) and related stored procedures (sp_insert,sp_delete)
Just one thing:I almost forget the most important thing, I "recently" (1 week ago) change "transaction retention" thrue EM from default value "at least 0 days but no more than 3 day" to "at least 3 days but not more than 5 days". But yestarday when I not
iced that replication works slower, I changed this propertis back to default value. But I did not noticed that database size was reduced.
Thank you Paul for any kind off help.
|||I would strongly advise against running sp_browsereplcmds. It is a very expensive and time consuming procedure to run when you have large numbers of row in the msrepl_commands and msrepl_transactions table.
You might want to look up sp_browsereplcmds in BOL where it talks about some of the swtichs on this command where you can limit the results set returned.
If I were you I would stop your SQL Server Agent on the Publisher/Distributor and then run the distributrion clean up agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- Frank wrote: --
Hi Paul
I don`t have anonymous subsribtions and there is no subscriber which has not recently synchronized with publisher.
I also noticed, that anythinks I do on the distribution database (querying etc) takes a lot of time. I am running maintainance plan periodicly, I dont see any problems with database.
sp_browsereplcmds returned 725005 records and I see "data" for all artices (8) and related stored procedures (sp_insert,sp_delete)
Just one thing:I almost forget the most important thing, I "recently" (1 week ago) change "transaction retention" thrue EM from default value "at least 0 days but no more than 3 day" to "at least 3 days but not more than 5 days". But yestarday when
I noticed that replication works slower, I changed this propertis back to default value. But I did not noticed that database size was reduced.
Thank you Paul for any kind off help.
|||Frank,
I was considering the use of sp_browsereplcmds to see if you have a definite
record that has been there for several days and has been distributed to all
subscribers. I know its a lot of transactions, but selecting a few at random
from the top of the table should confirm this.
The maintenance plan, if it is doing index maintenance should be disabled as
it takes out a table lock (dbcc dbreindex).
Also, in the current activity is there any evidence of blocking problems?
HTH,
Paul Ibison
|||Thank you Hillary
I done that, but there ware no records to delete from tables:
Here is the result:
Removed 0 history records from MSmerge_history.
Removed 0 history records from MSsnapshot_history.
Removed 0 history records from MSlogreader_history.
Removed 0 history records from MSdistribution_history.
Removed 0 history records from MSqreader_history.
Removed 0 history records from MSrepl_errors.
Removed 0 history records from sysreplicationalerts.
Removed 0 replication history records in 17.923 seconds (0 row/secs).
Would I damage replication system if I manualy delete data from MSdistribution_history tabel? I am almost sure, that this is the main problem. Because, when this tabel was small, replication worked much faster then now.
Thank you
|||I allready looked at the beging for blocking on database. But there is none.
|||you can safely whack the contents of msdistribution_history table.
The other tables I'd think twice or thrice about.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi Hillary
I will truncate table and we will see if this will gain any performance on replication speed.
I will inform any progress.
Thank you and Paul for your help.