11

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.

Improve this question
0
9

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

Improve this answer
1
  • That pretty much as I thought. I didn't see sp_dboption in 2012 so I assume it's been removed. I did find the SP in 2008 and indeed the option is present in 2008. I'll happily take your word that it can't be changed. I don't want to change it even if it weren't obsolete. Thanks for confirming, as well as explaining the concepts so well.
    – user3885315
    Sep 24 '15 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.