Replication distribution cleanup error

I used the SQL Server 2005 publication wizard to set up transactional replication. It scheduled a job called "Distribution clean up: distribution" that calls sp_MSdistribution_cleanup. The job fails every time it runs. The xp_cmdshell is enabled and SQL Server Agent runs under an account that has sufficient privileges to execute it. The account also has full rights to the repldata subdirectories. I have applied service pack 1.

I looked through the sp_MSdistribution_cleanup code and it looks like there are two stored procedures and one function missing from the SQL server databases. They are

master.dbo.sp_MSunc_to_drive sys.fn_escapecmdshellsymbolsremovequotes sys.sp_MSdrop_distribution_agentid

Where can I get a copy of these stored procedures? The expired distribution data is slowly consuming my remaining hard drive space. Thanks.

Joe

Hi, Joe,

There seems to be a bug in our code where we should not call master.dbo.sp_MSunc_to_drive, instead we should call sys.sp_MSunc_to_drive. In SQL Server 2005, we have move most of the system sps to mssqlsystemresource database, and some of them are hidden from end users as they're not supposed to be used directly by customers. sys.sp_MSunc_to_drive, sys.fn_escapecmdshellsymbolsremovequotes and sys.sp_MSdrop_distribution_agentid are hidden sps/function thus you don't see them but they do exist.

I will do more research and see if there is a workaround.

By the way, can you post the exact error message you're seeing from distribution cleanup job (by using replication monitor to view the job history)? Are you using dynamic snapshot merge replication? Any specific information about your replication setup would be helpful.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for answering me so quickly. The problem has been resolved. The account under which xp_cmdshell was executing did not have the proper access to the repldata share.

Joe

|||

Glad to hear that the problem is solved. The issue mentioned in my post is a minor issue and should not cause any functional problems. It would be helpful for other visitors of this thread if you can post the error message you're seeing before it's fixed (it should still be available in replication agent history).

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I'm really sorry, but the clean up agent already deleted the history. I think part of the message was "Could not clean up the distribution transaction tables."

Joe

sql