Remove or Disable Queue Reader Agent

Question

Remove or Disable Queue Reader Agent

skrilla99

SSCertifiable

Points: 5795
More actions
March 6, 2012 at 11:04 am

#265547

Hello,
I have two SQL Server 2008 R2 EE instances particpating in transactional replication. The setup is pretty vanilla, with no updating subscribers. However, when I re-deployed the publication the Queue Reader agent was created. I think this was added when I generated the script to create via SSMS, but I am not sure on this point.
I can't see any reason why this should be there and I would like to remove it, or at the very least disable it.
Any thoughts on whether or not this should be done and what the best method might be?
I see system stored procedures to drop each type of replication agent, EXCEPT the Queue Reader.
I would hate to just disable the job, but I also want to avoid recreating the publication.
Thanks,
Dan B

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

skrilla99 (3/6/2012)
Hello,
I have two SQL Server 2008 R2 EE instances particpating in transactional replication. The setup is pretty vanilla, with no updating subscribers. However, when I re-deployed the publication the Queue Reader agent was created. I think this was added when I generated the script to create via SSMS, but I am not sure on this point.
I can't see any reason why this should be there and I would like to remove it, or at the very least disable it.
Any thoughts on whether or not this should be done and what the best method might be?
I see system stored procedures to drop each type of replication agent, EXCEPT the Queue Reader.
I would hate to just disable the job, but I also want to avoid recreating the publication.
Thanks,
Dan B

Did you set this up as transactional replication with updateable subscriptions? If you chose to sync the changes in the queue, it will create this job,

Jared
CE - Microsoft

skrilla99 SSCertifiable Points: 5795 More actions · Answer 2

Hey Jared,

Thanks for the response. No updateable subscriptions.

I am doing some testing on a standby server and found that if I generate scripts via SSMS, it will add the Queue Reader Agent even if it wasn't there before. It is interesting to compare the differences in the scripts created when you first add a publication and when you choose to script out an existing one.

For example, the script generated at Create does not have an explicit call to create any agents. However the script created later does.

exec [Dan].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1

GO

exec [Dan].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1

GO

And it is that second exec that creates the queue reader...

Still not entirely sure that it is ok to remove it, but I suppose I am about 90% sure. Need some more testing before I remove it in production. Of course, I still don't know how I will remove it. I don't see a foil for sys.sp_addqreader_agent...

Sincerely,

Dan B

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Yes, if you are not using updateable subscriptions it is safe to remove it.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Jared SSC Guru Points: 61793 More actions · Answer 4

Yeah, it is a fairly generic script that you have to tailor to your needs. I would take that part out of the script. As far as removing it, you can either drop the publication and recreate it or try to delete the job.

Jared
CE - Microsoft

skrilla99 SSCertifiable Points: 5795 More actions · Answer 5

Thanks to you both.

That is what I figured, but I don't want to leave any metadata laying around that could bite me later.

-Dan B

Jared SSC Guru Points: 61793 More actions · Answer 6

skrilla99 (3/6/2012)
Thanks to you both.
That is what I figured, but I don't want to leave any metadata laying around that could bite me later.
-Dan B

That's why I think it is best to remove the publication through SSMS, to make sure you don't miss anything.

Jared
CE - Microsoft

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

skrilla99 (3/6/2012)
Thanks to you both.
That is what I figured, but I don't want to leave any metadata laying around that could bite me later.
-Dan B

Definitely a wise thing to do. QueueReader is not one of those that I have had cause me problems aside from that fact that it is another exe running that really isn't needed in most cases.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

skrilla99 SSCertifiable Points: 5795 More actions · Answer 8

OK... Well, I think what I will do is disable the job for now and rebuild the publication at the next maintenance window.

Thanks again to both of you.

Sincerely,

Dan B.