Wax off... wax on...

  • GilaMonster (8/14/2008)


    Having fun as an administrator today?

    Oh hell no! 🙂 I'm not a "systems" type of DBA and I've never used replication before. I'm one of them thar "application" DBA's... you know... brute force lives here! 😀

    I've got about 120 jobs, some that are Enabled, some that are not... some that are sheduled, some that are not. Guess I'll have to write a query to figure out which ones are disabled now so I don't reenable them later.

    Thanks again for the help... I may have to make a suggestion through the MVP network about making this an easier task... I can see needing to do it on a regular basis.

    Anybody see a problem with me just writing to the system tables for the jobs to disable/re-enable jobs as was their state prior to being disabled?

    --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 (8/14/2008)


    Oh hell no! 🙂 I'm not a "systems" type of DBA

    :hehe: Me neither

    Thanks again for the help... I may have to make a suggestion through the MVP network about making this an easier task... I can see needing to do it on a regular basis.

    This should be scriptable which would make it easy to do again. The only thing is figuring out the commands to run. I had a whole bunch of replication fixing and creating scripts, but that was two companies and four years ago.

    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 (8/14/2008)


    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?

    Seriously... I'm trying to archive 71% of the rows on a 36 million row table without causing the LDF to blow up. I've got a "delete crawler" (like I've recommended and used so many times) all ready to go... on my test system, the average delete rate was about 123k rows per minute... totally acceptable... deletes about a million rows every 4 1/2 minutes... perfect for an overnight run.

    I ran the crawler on a table of similar size that has no replication activity... moved right along at the predicted rate.

    I started the crawler on another table that has replication running against it... after an hour and 40 minutes, it still hadn't deleted the first million rows. No blocking and all the delete locks were as expected, it's just wicked slow.

    Just as a swag, I reduced the batch size to 40k rows... it'll only take about 35 hours to do the deletes WITH replication running... perfect for the weekend runs! 🙂 With that in mind, I think I've got this problem licked... but I am going to find a way to disable all Enabled jobs and then re-enable only those that were previously enabled... with a single nasty fast set based script. Sounds like an article in the making... 😛

    Thanks for the help, 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)

  • Oh yeah... almost forgot... the reason why I don't do the copy, delete, and rename thingy is because I don't know if it'll break replication or any of the associated jobs or not... any ideas on that?

    I'm also pretty much limited on disk space... can't make the MDF or the LDF much larger... copy of the 36M row table would probably stuff the system.

    --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)

  • Actually, stopping & disabling the LogReader Agent will also stop and disable the Job. (in fact it may be the same thing).

    [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 (8/14/2008)


    I'm also pretty much limited on disk space... can't make the MDF or the LDF much larger... copy of the 36M row table would probably stuff the system.

    I have to advise to reconsider then. When a replicated table's records change, their corresponding Log records cannot be freed from the log file until the LogReader reads them and copies them into the Distribution DB queue(s). Those 36 million Deleted rows are going to get copied into the LDF and stay there all weekend until you turn the LogReaders back on and they dig them out again. And that will probably take at least as long as it di to put them in there. Virtually nothing can undo that situation that does not cause problems that are even worse.

    [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]

  • Perhaps in between the delete batches restart the log reader, let it process the deleted rows, then backup the log, disable the log reader, delete, repeat.

    It's a lot of script and its going to take longer, but if the log fills the disk it's not going to be pretty.

    Other possible suggestion is to remove that table from the publication entirely. Put it back in after you've finished the deletes. That means that log records for that table won't be marked for replication and hence it may be safer to disable the log reader for the weekend (thought still not completely safe) as those records can be removed from the log after a log backup.

    Though on second thoughts, does removing an article from a publication require a reinit of the entire publication on SQL 2000?

    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
  • The problem is that in a Replicated environment, it takes both Backup and the LogReader to release a replicatable record from the LDF. Well, if you're in Simple mode, then only the LogReader.

    [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]

  • GilaMonster (8/14/2008)


    Other possible suggestion is to remove that table from the publication entirely. Put it back in after you've finished the deletes. That means that log records for that table won't be marked for replication and hence it may be safer to disable the log reader for the weekend (thought still not completely safe) as those records can be removed from the log after a log backup.

    Though on second thoughts, does removing an article from a publication require a reinit of the entire publication on SQL 2000?

    Oh yes. It does if any of the data has changed since you removed it. That was one of the things that I meant about "all other options are even worse". And a ReInit of a huge table is a seriously bad thing.

    [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]

  • No, no... the deleted rows aren't a problem... my appologies for not being a bit more clear (pretty much under the gun and not thinking correctly). The rows are being deleted from the target table of the replication and being move into an unindexed archive table. Not going to be a problem with the LDF...

    Also.... DOH! ... couldn't see the forest for the trees... what's the easiest way to disable all those jobs without having to remember which ones were enabled or not? Heh... turn off the bloody SQL SERVER AGENT SERVICE! DOH! DOH! DOH! :hehe:

    --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)

  • Ah. Well then, carry on. 🙂

    [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]

Viewing 11 posts - 16 through 25 (of 25 total)

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