Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
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? –  SuperCoolMoss Jul 5 '09 at 18:44
    
i think it's always required –  Nick Kavadias Jul 7 '09 at 1:37
    
Thanks Nick - this is a pre-production environment, I removed and recreated the replication and the job has been re-created. –  SuperCoolMoss Jul 9 '09 at 11:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.