SQL Server ‘Truncate Log on Checkpoint’ Option Explained

checkpointlogsql serversql-server-2012transaction

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB option named truncate log on checkpoint. Their claim is that it prevents the scripts from functioning and performing backups on the instance which is SQL 2012.

I feel that's complete BS since I cannot find any such option via sp_configure and backups are working everywhere but one instance. However I would like to remove a land mine, if that's what it is, and remove other obsolete elements. I do have them checking with the vendor but I don't have a high degree of confidence in anything that they say.

The research I did returned little more than it perhaps being an option for SQL 2000 or a Sybase option. Another claim was that it's called/used implicitly on later versions (2008 and up) when the recovery model is SIMPLE and there's no explicit option to turn it on or off.

Since the TRUNCATE LOG command is obsolete due to how transaction logs work these days, I think it's not an option that can even be queried at this point.

As I don't have any SQL Server 2000 instances about, I was hoping someone might recall this or could check it on one they have laying around. I've told them that it's nothing I can recommend. I was also hoping that someone can confirm this is obsolete.

Best Answer

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well

enter image description here

enter image description here

In SQL Server 2012 and up, you will get a nice error

Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_dboption'.

Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, SQL Server has a concept of recovery model that defines how the log truncation is honored e.g. In simple recovery - only a checkpoint will truncate the log where as in full recovery mode - only a log backup will truncate the log.

If you want to understand more about Transaction Log Management then refer to Stairway to Transaction Log Management

As s side note, SybaseASE even in newer version Sybase 15.7 has this option (As a matter of fact it has duplicate trunc log on chkpt and trunc. log on chkpt. the later is the one that SQL Server got in SQL 7.0 through SQL Server 2008R2 - seen in sp_dboption):

enter image description here

enter image description here

Related Question