Wax off... wax on...

Question

Wax off... wax on...

Jeff Moden

SSC Guru

Points: 1004434
More actions
August 14, 2008 at 1:02 pm

#94497

Does anyone know of a way to temporarily disable all replication jobs that are enabled (or make them skip until a certain time) and then re-enable only the ones that were disabled? I have some massive deletes to do to a 36 million row permanent table and replication is making the process substantially longer that it needs to be.
Thanks folks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

If it is Transactional Replication, then you (Stop & Disable) the LogReader job (from the Replication Interface, not the SQL Agent). This is the job that scrapes the Log file for relevant data changes and then queues them (usually) into the Distribution database. Stopping this will keep new data from being put into the queues, so to speak.

The subscription jobs will still be running pushing previously queued data out to the subscribers, until they run out of data to push.

Note the side-effects that this can have: the data in the log file will be retained until the LogReader reads it, or the publication is dropped. Even backing it up will not free it up. Also, when you start it up, you may get the proverbial "Snake swallowing an Elephant" effect, as it tries to move that massive amount of changes into Distribution and then out to all of the subscribers all at once. Your DB space can easily double or even triple, until it can clear this out, so have plenty of extra space available. Because if it runs out of space and fails, the whole interconnected replication system can come to a screeching, crashing halt that can take *forever* to recover from. (It took me 8 weeks to fully recover the environments from one of these last summer).

The advantage of this? A quick way to stop Replication (transactional) on a database without having to worry about the subscriptions becoming expired or invalidated (which effectively requires a rebuild to recover from).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

rbarryyoung (8/14/2008)
If it is Transactional Replication, then you (Stop & Disable) the LogReader job (from the Replication Interface, not the SQL Agent). This is the job that scrapes the Log file for relevant data changes and then queues them (usually) into the Distribution database. Stopping this will keep new data from being put into the queues, so to speak.

Sounds perfect, Barry... the problem is, I didn't even know how to spell replication about an hour ago... 😛

Where is the LogReader job you speak of? Is it in EM or??? Remember, I don't have a clue about replication so please be gentle. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Oh dear... I think I've found what you're talking about... it's in EM under {Replication Monitor}{Agents}{Log Reader Agents}... they've really really overdone it... there are 70 of the blighters... :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (8/14/2008)
Oh dear... I think I've found what you're talking about... it's in EM under {Replication Monitor}{Agents}{Log Reader Agents}... they've really really overdone it... there are 70 of the blighters... :blink:

There should be only (in fact there can be only ) one active log reader agent for a each database.

How many DBs on that instance?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

GilaMonster (8/14/2008)
Jeff Moden (8/14/2008)
Oh dear... I think I've found what you're talking about... it's in EM under {Replication Monitor}{Agents}{Log Reader Agents}... they've really really overdone it... there are 70 of the blighters... :blink:
There should be only (in fact there can be only ) one active log reader agent for a each database.
How many DBs on that instance?

79 not including the MS-4... appears that they have created 1 Log Reader Agent for every customer database... two of which are active constantly... the two each appear to have something to do with a database on a different server.

I'm goin to have to manually disable all 70 of these buggers, aren't I? :sick:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Aw crap! They've scheduled a job for every blood Log Reader Agent!!! I'm going to need to disable those buggers, too, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jeff Moden (8/14/2008)
79 not including the MS-4... appears that they have created 1 Log Reader Agent for every customer database... two of which are active constantly... the two each appear to have something to do with a database on a different server.

:blink:

There should be a way to identify which log reader is for which database, but I don't have Enterprise manager here. Can you right-click and get properties for those agents?

It's been a few years since I last debugged SQL 2000 replication.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (8/14/2008)
Aw crap! They've scheduled a job for every blood Log Reader Agent!!! I'm going to need to disable those buggers, too, huh?

That's done by default when replication is set up. The schedule should be 'Start when SQL Agent starts'.

Emphasis on should.

Having fun as an administrator today?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (8/14/2008)
Aw crap! They've scheduled a job for every blood Log Reader Agent!!! I'm going to need to disable those buggers, too, huh?

It should, if I recall correctly, be enough to just stop the appropriate job. That should stop the agent. The agents are run and controlled by the jobs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Now, now, be nice to the OP, Gail. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

Ok... thanks Gail. And, heh... thanks for not killing the OP for his ignorance in this area. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

rbarryyoung (8/14/2008)
Now, now, be nice to the OP, Gail. 😀

I thought I was being nice 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

OK, there are several places that you can do this from, but the best place is: "Replication Monitor/Agents/Log Reader Agent" which will all of the LogReaders and only the LogReaders. You want to Disable them (right-click, Agent properties, uncheck "Enable" in the middle of the dialog), then Stop them (right-click, Stop).

Times 70, I guess.

Don't suppose I could ask "What are you Really trying to do?" could I? 🙂

Seriously, though, how is Replication interfering? Do you mean, in general load, or is it just on the DB that you want to execute against? Maybe you could just disable it there?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

I ask the above question, because I have had that happen to me: Doing a massive insert/update/delete, and the LogReader starts trying to scrape the log while the transaction is still going on. It can get really slow.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]