Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Unanswered: Switching in and out of Bulk Logged mode

    I sometimes have problems when running index rebuilds due to the tran log growing too big. MS suggest switching the db from full recovery mode to bulk-logged mode fur the duration of the rebuild. Question is, when you switch a db from full recovery to bulk logged, does that invalidate the tran log backups up to that point? When the db is switched back into full-recovery mode, are the following tran log backups still in sequence?


  2. #2
    Join Date
    Apr 2004
    Kansas City, MO
    You posted this on with less information. My preference during your maintenance window would be this.

    1. Switch off transaction log backups.
    2. Switch to simple, which will even be faster than bulk-logged.
    3. Run the index rebuilds.
    4. Switch to full recovery mode.
    5. Run a full backup.
    6. Start the transaction log backups.
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    Turning Simple Recovery mode on does not affect the speed at all. The only thing that is affected is .......... actually, nothing is affected, because if reindexing makes the log grow, it'll grow regardless of the Recovery Mode setting, because it's "transaction", which means until it completes, - the log has to retain everything that pertains to it (after all, that's the reason the log will grow in the first place, right?!!!)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts