I’m trying to change the maximum transaction retention period in our SQL2005 tranasaction replication environment from the default 72 hours to 120.
Either through T-SQL or GUI I get the following message:
Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 25
Supply either @job_id or @job_name to identify the job.
The T-SQL I’m running is a as follows:
sp_changedistributiondb @database = ‘distribution’, @property = ‘max_distretention’, @value = 120
Any ideas anyone?
I think the code below is what’s failing. I got this by running sp_helptext sp_changedistributiondb & also looking at what sproc’s call sp_verify_job_identifiers.
It looks like changedistributiondb is having trouble updating the distribution cleanup agent job when it runs sp_update_jobstep :
SELECT @command = 'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + CONVERT(nvarchar(12), @new_min_distretention) + ', @max_distretention = ' + CONVERT(nvarchar(12), @max_distretention) EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, @command = @command
maybe this job has been deleted or there is something else strange going on, either way, you should be able to modify the job step manually.
EDIT: The cleanup agent runs on the server with the distribution database. It removes replication history and runs every 10 minutes by default. The job should look like this:
name: Agent history clean up: distribution
category: REPL-History Cleanup
steps: (1)Run Agent
command:EXEC dbo.sp_MShistory_cleanup @history_retention = 48
- SQL Server 2005 Replication Subscription Expiring Warning
- SQL 2005 Replication Error – Upgraded SP3
- In SQL Server 2005/2008 Merge Replication, what permissions are necessary for the Merge Agent account?
- Sql Server 2005 Replication: How to setup subscription with a sql script?
- Creating indexes on tables filled from transactional replication – SQL 2005