Truncate log on checkpoint

Question

Truncate log on checkpoint

rossc

Say Hey Kid

Points: 699
More actions
March 1, 2005 at 3:32 am

#109626

Hi guys
More of a discussion than a question.
Is there any reason not to set truncate log on checkpoint on a database?
thanks in advance
Ross

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

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

David.Poole SSC Guru Points: 76029 More actions · Answer 1

If you rely on transaction log backups then you can't use it.

If you are in a heavy production environment then you may want more control over the truncation of the log, possibly for performance reason.

I tend to set it in my development environment only for precisely these reasons

LinkedIn Profile

rossc Say Hey Kid Points: 699 More actions · Answer 2

Hi David

when you say if you rely on transaction logs, what do you mean?

I have, like you, set it in dev. Our live environment uses FULL recovery. We do 30min rolling transaction log backups. How would this effect the live environment, were we to implement it?

thanks

R

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=16769

Read Jonathan's response there. And ignore the strange formatting as this was written in the former forum software package and migrated.

For your question specifically, here's the management summary:

1. Production OLTP databases should almost never be set to truncate on checkpoint. The only advantages to this setting are:

a) that you need not worry about maintaining (i.e. backing up) the tran log, and

b) it may make running out of disk space less likely.

The disadvantages are:

a) slower performance due to the truncate process running,

b) no point in time recoverability, and

c) no recovery to time of failure.

If you decide that recoverability to the last full or differential backup is "good enough" for your environment, then you are forsaking sophistication for convenience.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

athurgar SSCrazy Points: 2816 More actions · Answer 4

In a production environment it is the businesses call on its recoverability - not the DBAs, in line with the SLAs. But as the DBA you need to understand what the different options are so you can present them with pros and cons.

In dev where I am in control - everything is set to simple.

In UAT/QA/DR it mirrors production,