Silver Award Winner  Award in March 2019


1. First check free space in Distribution database 

use distribution
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
        (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
SELECT Publication,
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

  1. As a first step I checked if xp_cmdshell is configured or not. It was found that xp_cmdshell was indeed enabled.
  2. 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
  3.   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.
  4. Error messages are little misleading type and decided to go ahead with verifying the security permission of the UNC share. \\MachineName\ReplData
  5. Security permissions of the unc share, and assigned both the Agent account and the Database Engine  account full control
  6. Run the job again and it failed yet again.
  7. 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.
  8. 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
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)
   set @eDate = '2019-02-20'  --Enter date
while @date <= @eDate
   begin tran
       delete from msMerge_history
       where [time] < @date
   commit tran
   set @date = dateadd(dd, 1, @date)
set nocount off

check other jobs.

select s.job_id,,s.enabled, as categoryname
from msdb.dbo.sysjobs s inner join msdb.dbo.syscategories c on s.category_id = c.category_id
where 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.


See Also