Rename Distribution job

Whenever we create a replication (transaction replication) whether it is from Wizard or script, the respective agent (log reader, distributor and snapshot) job created automatically with their default naming conversion as

Snapshot Agent:
[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]

Log reader Agent:
[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]

Distributor Agent:
[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]

We can only schedule the job. But no control to rename the job.

Now here, what if for some reason we have to rename the job, we have two ways to do so.

1. GUI: goto agent jobs and right click and rename the jobs as we wish…simple

2. But what if we require to make it automated and renames the job with our script of replication.

I have shared steps/script for replication in my earlier post here , (we can also generate this script from existing replication)

Now we have the script. For renaming the distribution job we can follow steps:

A. Run the script and after that renames the job as

declare @JobID uniqueidentifier
select @JobID=job_id from sysjobs where category_id =(
select category_id from syscategories where name=’REPL-Distribution’) –generally 10

exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N’abc’

 above script works great if we have only one replication setup on our server.

What if we have multiple publishers and subsribers setup on one server then we will be having multiple Distribution jobs, for such cases

Using “distribution.dbo.MSdistribution_agents” system table, which gives information about the distribution agent with respect to sysjobs.

following query will return distribution job, which we have created by our script.

declare @JobID uniqueidentifier

select @JobID=job_id from distribution.dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db=[‘PublisherDB’]  and
a.subscriber_db=[‘Destination_DB’] and
a.subscriber_id=s.srvid and
s.srvname=[‘SubsriberServer’]

–this will give jobid for the replication we have created, just use that jobid and rename the job.

exec msdb.dbo.sp_update_job @job_id = @JobID, @new_name = N’NewJobName’

In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.

Hope I could explain this properly.

Thanx.

Vinay

Advertisements
This entry was posted in Others, Replication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s