SQL Server Troubleshooting: Distribution database size is increasing

Article
1/17/2024

https://msdnshared.blob.core.windows.net/media/2016/08/0841.NinjaAwardTinySilver.png Award in March 2019

1. First check free space in Distribution database

use distribution
go
select growth,   name,filename,
        size*8.0*1024/(1024*1024) as  TotalMB,
        fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) as  UsedMB,
        size*8.0*1024/(1024*1024) -
        fileproperty(name,'SpaceUsed')*8.0*1024/(1024*1024) as  FreeMB
from
        sysfiles
where
        (status & 0x2) <> 0 --and filename like ' %'

There is free space in mdf and ldf files. We will shrink according.

2. Check Log or MDF is increase and check any open transaction

dbcc opentran

3. Check replication type

use from query

use distribution
go
SELECT Publication, 
CASE
WHEN Publication_type = 0 THEN  'Transactional'
WHEN Publication_type = 1 THEN  'Snapshot'
WHEN Publication_type = 2 THEN  'Merge'
End TypeOfReplication
FROM MSPublications

you can see publisher property where you can easily trace for replication type.

4. Check if SQL Agent job [Distribution clean up: distribution] is running properly.

This job clear old transaction information from distribution database.

if this job is failed.
above job run following code.

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

Following code is present in job :-

EXEC dbo.sp_MShistory_cleanup @history_retention = 48

A. distribution job failing with error – Could not remove directory

Troubleshooting steps

As a first step I checked if xp_cmdshell is configured or not. It was found that xp_cmdshell was indeed enabled.
When this is triggered from a job the Agent account is used, hence decided to run this from SSMS query window. I got the same error message as mentioned above, along with this
Replication -@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables.
Error messages are little misleading type and decided to go ahead with verifying the security permission of the UNC share. \MachineName\ReplData
Security permissions of the unc share, and assigned both the Agent account and the Database Engine account full control
Run the job again and it failed yet again.
I decided to do some R&D via web and found this blog post from SQL Server Support team. This post was pointing that the SQL Account should also have full control for the UNC share.
Granted full control to SQL Account for the UNC share and the issue was resolved.

B. Clean up is not clean history table.

if we check large tables

MSmerge_history will keep log of every transaction that has happened since the last snapshot was generated so that replications can be rebuilt without generating new snapshots (within reason). But it also appeared that if a subscription was set to continuous then it was also not deleting any of the transactions in MSmerge_history and therefore these transactions were going back months and months.

We can delete manually record using following code.

use distribution
go
 
set nocount on
 
declare @date datetime, @eDate datetime, @volume int
select @date = convert(datetime, left(min([time]), 11))
from msMerge_history
 
set @eDate = dateadd(dd, 1, @date)
 
if @eDate >= DATEADD(dd, -3, current_timestamp)
begin
   set @eDate = '2019-02-20'  --Enter date
end
 
while @date <= @eDate
begin
   begin tran
       delete from  msMerge_history
       where [time] < @date
   commit tran
 
   set @date = dateadd(dd, 1, @date)
end
 
set nocount off

check other jobs.

select s.job_id,s.name,s.enabled,c.name as  categoryname 
from msdb.dbo.sysjobs s inner join msdb.dbo.syscategories c on  s.category_id = c.category_id
where c.name in ('REPL-Merge','REPL-Distribution','REPL-LogReader')

5. check retention

set transaction  isolation level  read uncommitted
select distinct
srv.srvname publication_server 
, a.publisher_db
, p.publication publication_name
, p.retention
, ss.srvname subscription_server
, s.subscriber_db
from MSArticles a 
join MSpublications p on a.publication_id = p.publication_id
join MSsubscriptions s on p.publication_id = s.publication_id
join master..sysservers ss on s.subscriber_id = ss.srvid
join master..sysservers srv on srv.srvid = p.publisher_id
join MSdistribution_agents da on da.publisher_id = p.publisher_id 
and da.subscriber_id = s.subscriber_id
ORDER BY  p.retention

6.Retention duration:

Check property and retention is correctly set.

select **Distributor Properties **

Check transaction retention is set in hours only.

Now In MSSQL 2017 you can able retention command in one window only.

Share via