Posted by Erland Sommarskog on 10/01/17 11:32

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> Hi Erland,
>
> mmmm.... 'kay, I guess you're right.
>
> But then, what DOES the quote that Ronin posted mean?

Just because it is in a guru's guide, does not mean that it is right.
Ken Henderson usually knows his stuff, so it is a little surprising.
Then again, Ronin did not mention issue year or anything. This is an
area where things have changed over time. The statement might have been
true in 4.x. (I recall that it was documented that way. But when you
think of it, it does not make sense. TRUNCATE TABLE is just like DROP
TABLE, except that you keep the metadata.)

I looked up an old discussion in our internal MVP forum, and found
that at the time I had myself conducted a small experiment:

* Set "truncate log on checkpoint" to false.
* DUMP DATABASE
* TRUNCATE TABLE tbl1
* UPDATE tbl2 SET ... WHERE ...
* DUMP TRANSACTION
* LOAD DATABASE
* LOAD TRANSACTION

And this worked well. On SQL 6.5 I should add.

In the same thread I found a statement from an SQL Server dev who said
there is really only one command to break a backup/log chain and that is
ALTER DATABASE SET RECOVERY SIMPLE. (OK, so in SQL 2000 there is also
BACKUP TRANSACTION WITH TRUNCATE_ONLY/NO_LOG. But in SQL 2005, these
commands have no effect.) No DML or DDL on a single table can break
the log chain.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]