SQL Server 2005 Replication: Job missing: Distribution clean up: distribution job RRS feed

  • Question

  • I am reading on how to stop my distribution db from growing wildly, in doing this I find that there is a job I am supposed to have called:  Distribution clean up: distribution job

    Is there any reason why I wouldn't have this in SQL SRVR 2005 STD?

    How do I go about adding it in?

    Monday, August 6, 2012 7:42 AM


All replies

  • I'm not sure why this job doesn't exist on your SQL Server (may be some one deleted this job by mistake). Follow these steps to recreated this job:

    1. Right-click Replication folder and choose Properties.
    2. Click on browse button and then choose values as follow:

    3. Click OK.

    This will create the distribution clean job again on the server.


    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    Monday, August 6, 2012 12:28 PM
  • It should be there - if you are running the express edition it will not be there.

    If not just schedule a job that runs the following command:

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

    Run it every 10 minutes.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Monday, August 6, 2012 12:38 PM
  • Thank you both, I will run the cleanup script first then add the job once its done and let you know!
    Tuesday, August 7, 2012 5:56 AM
  • I ran this and it cleaned up plenty! The distribution DB shrank significantly!
    Wednesday, August 8, 2012 6:59 AM
  • Following the above procedure, caused the following error:

    TITLE: Distributor Properties

    An error occurred applying the changes to the Distributor.

    For help, click:


    Property "min_distretention" update with value "0" failed. (Microsoft.SqlServer.Rmo)


    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


    Supply either @job_id or @job_name to identify the job.
    Changed database context to 'master'. (Microsoft SQL Server, Error: 14294)

    For help, click:



    Thursday, December 5, 2013 1:14 PM
  • I am getting the same error when tried to set up the retention period.  Any one found solution for the same?
    Thursday, May 9, 2019 3:18 PM