Post reply

Stopping transactional logging

  • May 8, 2008 at 12:04 pm

    #186303

    I have a stored procedure the processes about 5 million rows many times as it moves these rows through a series of filters and temp tables to summary tables. These tables are in a database that is currently set to the Full Recovery Model. Because of the recovery model this job is generating a HUGE transaction log. Is there a way to temporarily put this stored procedure into some sort of simple mode so everything that it processes is not logged once the transaction completes?

  • May 21, 2008 at 9:32 pm

    #818968

    Yep... move the process to a data base that uses the SIMPLE recovery mode. If you don't have one... make one... call it "ScratchPad" or "SandBox" or something like that. When you're all done with the processing, do a single update on the table in the "real" database.

    If you have a TempDB like I do on the production box (12 gig), you might be able to do it all in temp tables.

    --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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

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