MSrepl_commands table is increasing continuously

Question

Dear All,

We are using Sql server 2005 stsndard eddition.

We aer maintaining Transactional replication with update.

Form 3 days onwards we are observing MSrepl_commands is increasing.And the jobs are running fine.

It is having 1 croe records.Due to this disribution DB size is increasing.

Please help me to find out the root cause and solve this issue.

Thanks in advance.

Answer 1

Are you sure that the distribution cleanup job has been running?

---

Chuck

Answer 2

You may want to turn immediate sync off on your publicatons/subscriptions.

---

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 3

If the job is running then have a look at this article

http://www.replicationanswers.com/TransactionalOptimisation.asp

---

Chuck

Answer 4

Hi oldjeep,

Yes distribution cleanup job is running without any fail.

Answer 5

Hi oldjeep,

As mentioned in  link i have checked 'immediate_sync' status of publisher.Value is 'Zero'.

Yesterday distrubution size is 4 gb.But today it came to 2gb.Msrepl_commands table size is decreased.

Please guide me .

Answer 6

Hi Hilary ,

I checked the 'immediate_sync' status of publisher.Value is 'Zero'.Please suggest me.

Answer 7

Are you saying that immediate sync was already set to zero or are you saying that you just changed it to zero?

---

Chuck

Answer 8

I didn't change the value.Already it is set to zero.

Answer 9

Large distribution datbases can be a problem with the immediate sync option. It sounds like you now have this setting set to 0 (false) and your distribution database size is under control.

Monitor it over time and see if it is still a problem. 2 Gigs is nothing for a database size today.

---

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