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

    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

Answers

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.


    Regards,

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

    http://basitaalishan.com


    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? 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

    Monday, August 6, 2012 12:38 PM
    Moderator
  • 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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5000&EvtSrc=MSSQLServer&EvtID=14294&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    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