1

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?

1 Answer 1

0

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
database: distribution
**command:**EXEC dbo.sp_MShistory_cleanup @history_retention = 48

3
  • Thanks Nick, it looks like the distribution clean-up job is missing. Any ideas on how to recreate it? Can't remember it being there - perhaps I'm running a configuration where it isn't required - distributor co-located with publisher? Jul 5, 2009 at 18:44
  • i think it's always required Jul 7, 2009 at 1:37
  • Thanks Nick - this is a pre-production environment, I removed and recreated the replication and the job has been re-created. Jul 9, 2009 at 11:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .