Distribution Agent Disappeared

Question

Post reply

Distribution Agent Disappeared

Congruent Influence

Default port

Points: 1416
More actions
November 5, 2002 at 3:04 pm

#80259

Recently, I stopped SQL, copied mdf,ndf,ldf files to a new directory, started sql and attached the new database on the same server. The database I copied was a subscriber database in transaction replication. When attaching the new database, the distribution agent got confused and disappeared all together. It was not under jobs or distribution foler. Not having time to rebuild replication from the ground up, I created a scheduled job to act as the distribution agent. This seems to have band aid fixed the issue. The problem I now have is going to the distribution folder and right clicking the dist. agent and clicking on agent properties. I get the error:
Error 14262: The specified @job_id('....') does not exist.
Is there a way to re-associate the distribution agent to the new scheduled job that I created?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

I think you can get the job ID from the sysscheduledjobs table in MSDB database.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Sorry no SQL server on hand, but I believe there is a distribution agent table in the distribution database that references the job, that is how they link up. However, to tell for sure where it is looking, start SQL Profiler and look at SQL Stmt starting and Exceptions, which will occurr because of these problem, then kick it off the agent in EM and see what all is going on in the background. Personally, I would drop this publiction and rebuild to make sure fixed properly.

Congruent Influence Default port Points: 1416 More actions · Answer 3

Thanks to you both for contributions. Below are my findings:

--Look for a specific job and convert the jobid to binary, in my case the distribution agent

select name, convert(binary, job_id), job_id from msdb..sysjobs where ...

--Look at what the subscriber db thinks the dist agent is. The agent id is the converted binary value of the job_id from sysjobs

select distinct distribution_agent, agent_id from [subscriberdbname]..msreplication_subscriptions

--I received a specific error with the job_id it was looking for. Convert it to binary to ensure a match of what subscriber thinks the dist agent should be.

declare @j-2 uniqueidentifier

set @j-2 = '8522F5CC-53C1-4B28-BDE1-F41D14FE056F'

select @j-2 as job_id, convert(binary, @j-2) as agent_id

From this information, you should be able to update the job_id in sysjobs to be the job_id listed in the error. Alternatively, you can change the binary value of the job id in the msreplication_subscriptions table. Either of these should fix the problem.

For further information, the following procedures were called to find out this information:

-- exec [yourdbname].dbo.sp_helppullsubscription @publisher = N'...', @publisher_db = N'...', @publication = N'ALL'

-- exec msdb..sp_get_sqlagent_properties

-- exec msdb..sp_help_job

-- exec msdb..sp_help_job @job_id = 0x....., @job_aspect = N'job'

Thanks again to leading me to the profiler to find this info.

Remember this when a developer tells you it will just be temporary. Temporary = Permanent.