where did the truncate log on checkpoint option go?

Question

where did the truncate log on checkpoint option go?

neotokyo

SSCertifiable

Points: 7239
More actions
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.
_________________________

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

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)

MohammedU SSCoach Points: 19644 More actions · Answer 2

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

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

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)