Post reply

where did the truncate log on checkpoint option go?

  • March 11, 2007 at 4:15 pm

    #172638

    i remember in sql 2000, that i could simply set the

    'truncate log on checkpoint' option from the 'options'

    tab. in sql 2005 management studio i don't see this

    listed under 'options'

    where did it go?

    i suppose i could just use sp_dboption 'mydb', 'trunc. log on chkpt', 'true' unless it's been depricated in 2005

    but... where is this available from GUI? not that i prefer

    gui, but just wondering.

    _________________________

  • March 11, 2007 at 7:14 pm

    #694285

    It's been "summarized" into the "Recovery Mode".  I believe the "Simple" recovery mode enables it (and SELECT INTO/Bulk Copy, etc).

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

  • March 11, 2007 at 9:21 pm

    #694291

    You can't set TRUNCATE LOG ON CHK POINT through EM in 2000 and the same way in 2005....but it is included in recovery model as Jeff mentioned...

    Where as you can set this using the QA or Query Window...

    sp_dboption

    'pubs', 'trunc. log on chkpt.', true

     

    MohammedU
    Microsoft SQL Server MVP

  • March 11, 2007 at 9:48 pm

    #694293

    Please folks... make sure you really, really know what you're doing... if you set 'trunc. log on chkpt.' to true, you have a little surprise coming...  try this...

    Set the recovery model to "FULL" on pubs using EM.  Do a refresh and then double check that it's actually set to "FULL".

    Then, run the sp_dboption 'pubs', 'trunc. log on chkpt.', true command. 

    Now, go back to EM, do a refresh, and see what the recovery model is set to... SURPRISE!!!

    --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 4 posts - 1 through 3 (of 3 total)

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