Home / server / Questions / 35552
Accepted
SuperCoolMoss
Asked: 2009-07-04 05:54:45 +0800 CST

SQL 2005 Replication error

  • 772

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?

sql-server sql-server-2005 replication

1 Answers

  1. Best 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

    • 0