How do you stop Replication?

Question

Post reply

How do you stop Replication?

Welsh Corgi

SSC Guru

Points: 116520
More actions
June 29, 2015 at 8:42 am

#299524

How do you stop Replication?
It is causing blocking.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 40 total)

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

Justin Manning SA Hall of Fame Points: 3190 More actions · Answer 1

right click on the publisher, View Log Reader Agent Status... STOP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I had to stop the Snapshot Agent and Log Reader Agent.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I missed one.

Any input would be greatly appreciated?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

This command is executing.

It seems to be related to replication.

How do I prevent this command from executing? It is blocking another process.

How do I stop replication entirely?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

How do I turn this off?

update MSsubscriptions

set status = @INACTIVE

where agent_id in (

select derivedInfo.agent_id

from (

-- Here we are retrieving the agent id, publisher database id,

-- min subscription sequence number, and the transaction seqno

-- related to the max timestamp row in the history table. this is

-- important since the tran seqno can go back to lower values in

-- the case of reinit with immediate sync.

select s.agent_id as agent_id,

s.publisher_database_id as publisher_database_id,

min(s.subscription_seqno) as subscription_seqno,

isnull(h.xact_seqno, 0x0) as xact_seqno

from MSsubscriptions s

left join (MSdistribution_history h with (REPEATABLEREAD)

join (select agent_id,

max(timestamp) as timestamp

from MSdistribution_history with (REPEATABLEREAD)

group by agent_id) as h2

on h.agent_id = h2.agent_id

and h.timestamp = h2.timestamp)

on s.agent_id = h.agent_id

where s.status = @ACTIVE

and s.subscriber_id >= 0 -- Only well-known agent

group by s.agent_id, -- agent and pubdbid as a pair can never be differnt

s.publisher_database_id,

isnull(h.xact_seqno, 0x0)-- because of join above we can include this

) derivedInfo

where @cutoff_time >= (

-- get the entry_time of the first transaction that cannot be

-- cleaned up normally because of this agent.

-- use history if it exists and is larger

case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno

then

-- join with commands table to filter out transactions that do not have commands

isnull((select top 1 entry_time

from MSrepl_transactions t,

MSrepl_commands c,

MSsubscriptions sss

where sss.agent_id = derivedInfo.agent_id

and t.publisher_database_id = derivedInfo.publisher_database_id

and c.publisher_database_id = derivedInfo.publisher_database_id

and c.xact_seqno = t.xact_seqno

-- filter out snapshot transactions not for this subscription

-- because they do not represent significant data changes

and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT

or (c.xact_seqno >= sss.subscription_seqno

and c.xact_seqno <= sss.ss_cplt_seqno))

-- filter out non-subscription articles for independent agents

and c.article_id = sss.article_id

-- history xact_seqno can be cleaned up

and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )

and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )

order by t.xact_seqno asc), @max_time)

else

isnull((select top 1 entry_time

from MSrepl_transactions t,

MSrepl_commands c,

MSsubscriptions sss

where sss.agent_id = derivedInfo.agent_id

and t.publisher_database_id = derivedInfo.publisher_database_id

and c.publisher_database_id = derivedInfo.publisher_database_id

and c.xact_seqno = t.xact_seqno

-- filter out snapshot transactions not for this subscription

-- because they do not represent significant data changes

and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT

or (c.xact_seqno >= sss.subscription_seqno

and c.xact_seqno <= sss.ss_cplt_seqno))

-- filter out non-subscription articles for independent agents

and c.article_id = sss.article_id

-- sub xact_seqno cannot be cleaned up

and t.xact_seqno >= derivedInfo.subscription_seqno

and c.xact_seqno >= derivedInfo.subscription_seqno

order by t.xact_seqno asc), @max_time)

end))

UPDATE MSdistribution_history SET runstatus = @runstatus, time = @current_time,

duration = @duration, comments = @comments,

xact_seqno = @xact_seqno, updateable_row = @this_row_updateable,

error_id = case @error_id when 0 then error_id else @error_id end

WHERE

agent_id = @agent_id and

timestamp = @lastrow_timestamp and

( runstatus = @runstatus or

(@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I take it I'm on the No Fly List. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I'm enjoying talking to myself.:hehe:

The blocking issues are much better.

Would I do something like this and the other 2 Tables?

select * From distribution..MSsubscriptions

--- based on the above values, run below statement

--- this can be run using SQLAgent job

if exists (select 1 from distribution..MSsubscriptions where status = 0)

begin

UPDATE distribution..MSsubscriptions

SET STATUS = 0

WHERE publisher_id = '--publisher_id -- will be integer --'

AND publisher_db = '--publisher db name ---'

AND publication_id = '--publication_id -- will be integer --'

AND subscriber_id = '--subscriber_id -- will be integer ---'

AND subscriber_db = '-- subscriber_db ---'

end

else

begin

print 'The subscription is not INACTIVE ... you are good for now .... !!'

end

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ozzmodiar Ten Centuries Points: 1315 More actions · Answer 8

If it's temporary; you can just disable the associated SQL agent jobs. Then adjust the schedule so they run at a time when the server is not under load.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Ozzmodiar (6/29/2015)
If it's temporary; you can just disable the associated SQL agent jobs. Then adjust the schedule so they run at a time when the server is not under load.

Now I'm being told to turn it back on.

I shut everything down.

Apparently it was updating since Friday.

I stopped everything.

Could you please tell me the steps I need to take and in what order please?

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ozzmodiar Ten Centuries Points: 1315 More actions · Answer 10

Well it's going to depend on what steps you used to bring it down. You had a few interesting replies. Did you implement anything that was discussed?

If you've just disabled the agent jobs, it will just a matter of re-enabling them.

Assuming you have not had them disabled beyond the "Transaction Retention" setting in the distributor properties, they will just pick up where they left off and carry on.

It may be worthwhile to stagger the re-activation of each job in order to offset the load on the server if you're still seeing issues.

And you will still need to review the scheduling for each subscription agent job (assuming push subscriptions) and ensure they are not running during peak times; or if they are, ensure you have the resources to handle it.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

I turned off the SQL Agent and Reader Agent.

I turned off Synchronization.

After that I disabled the jobs.

I basically shut everything down. :blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

CPU was at 100% and memory

was at 97% before Replication was turned on.

The users was the data refreshed within seconds.:w00t:

So the Server needs beefed up.

Thanks for your advice.

Have a good day.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 13

Hi,

what kind of replication do you use?

And, if you use transactional replication, do you use push or pull. It is very difficult to tell you, how to start replecation, if we don't know you setup.

Snapshot-Agent is used, if new objects should replicated. It is not necassary to run all the time

LogRead-Agent should run first, because this agents reads the log from the publisher.

Distribution-Agent should run at last, because this agents writes the log to the subsriber database.

You get a lock of blockings, if many replications use the same distribution database. Can you tell us, how many replications with how many article you use?

Andreas

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Hi,

It is push transactional replication.

There is only one instance of replication.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/