Aug 23, 2011
tom
No Comments

SQL 2005 Replication error

Question

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?

Answer

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

Check more discussion of this question.

Related posts:

  1. SQL Server 2005 Replication Subscription Expiring Warning
  2. SQL 2005 Replication Error – Upgraded SP3
  3. In SQL Server 2005/2008 Merge Replication, what permissions are necessary for the Merge Agent account?
  4. Sql Server 2005 Replication: How to setup subscription with a sql script?
  5. Creating indexes on tables filled from transactional replication – SQL 2005

Leave a comment