Distribution DB Increasing in Size

Question

We have a transaction replication running. We then decided that on a certain application, they would enable a module that would increase data in one of the tables. Prior to this, the Distribution was only 10GB, after 3 days, it is now 27 GB and constantly increasing. I see on the autogrow event that it keeps adding space every so often. Then doing some research, I found out that my MSRepl_Commands has a 101,743,645 record count and adding up.

Both SQL JOBs Agent history clean up: distribution and Distribution clean up: distribution are running every 10 mins.

Doing some research, I decided to change the default values below as follows:

Transaction retention 72 to 48
History retention 48 to 24

That brought down MSRepl_Commands record count to 71,317,990 and freed up unallocated space when it was all purple prior to.

I then started changing the batch size values but the MSRepl_Commands record count is still increasing.

79120832 2020-09-08 13:34:35.900
78914968 2020-09-08 13:18:11.937
79011913 2020-09-08 13:03:39.010
78766061 2020-09-08 11:48:18.950
78773339 2020-09-08 11:38:05.873 <-- 500000 / 20000 (after this count)
78707304 2020-09-08 11:07:24.200
78665972 2020-09-08 10:57:04.197
78675084 2020-09-08 10:47:29.220 <-- 300000 / 10000
78663345 2020-09-08 10:37:29.283
78634309 2020-09-08 10:27:31.910 <-- 200000 / 10000
78582092 2020-09-08 10:16:54.253
78579591 2020-09-08 10:07:14.683
78597272 2020-09-08 09:49:16.243 <-- 100000 / 10000
78511296 2020-09-08 09:37:04.020 <-- 50000 / 2000
78484965 2020-09-08 09:27:25.747
78578448 2020-09-08 09:22:05.663
78386876 2020-09-08 08:58:22.830 <--25000 / 10000
78452788 2020-09-08 08:51:52.320
78274145 2020-09-08 08:37:29.053 <-- 10000 /4000 (Batch size)
78318959 2020-09-08 08:30:59.583
78211886 2020-09-08 08:27:15.527
78224751 2020-09-08 08:19:09.980
71317990 2020-09-04 12:53:07.813 <-- 72 to 48 / 48 to 24 (transaction / history retention)
101743645 2020-09-04 12:30:25.580
101555195 2020-09-04 12:15:32.463

Any possible issue if I lower down transaction / history retention to let's say half 24/12?

What else to look at?

TIA

Answer 1

Hi @Alex P ,

Any possible issue if I lower down transaction / history retention to let's say half 24/12?
What else to look at

No possible issue. And this would be helpful.

You can also try code from this case: distribution-database-is-huge-in-size

use distribution

GO
declare @table table(PK int identity primary key, xact_seqno varbinary(16))
declare @rowcount int
declare @csgo int
declare @xact_seqno varbinary(16)
set @csgo =1
insert into @table
select xact_seqno from msrepl_transactions
where entry_time<getdate()
select @rowcount=@@rowcount
while @csgo <@rowcount
begin
select @xact_seqno =xact_seqno from @table

delete from msrepl_commands where xact_seqno=@xact_seqno
select @csgo =@csgo +1
end

And More information: sql-server-troubleshooting-distribution-database-size-is-increasing

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Alex P ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.