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?

Answer 1

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

Answer 2

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

Answer 3

Thank you both, I will run the cleanup script first then add the job once its done and let you know!

Answer 4

I ran this and it cleaned up plenty! The distribution DB shrank significantly!