Log Invalidated after truncate table

[ronin 47th]

Hi group,
In one of the books 'Gurus Guide to Transact SQL' i found this info:

------------------------------------------------------------
TRUNCATE TABLE empties a table without logging row deletions in the
transaction log. It can't be used with
tables referenced by FOREIGN KEY constraints, and it invalidates the
transaction log for the entire database.
Once the transaction log has been invalidated, it can't be backed up
until the next full database backup.
------------------------------------------------------------

Does it mean that the log backup taken after table truncation is an
invalid backup?

Help me! i'm lost....

Thanks in advance
Ronin

*** Sent via Developersdex http://www.developersdex.com ***

[Hugo Kornelis]

On Thu, 17 Nov 2005 20:03:10 GMT, ronin 47th wrote:

Hi group,
In one of the books 'Gurus Guide to Transact SQL' i found this info:

------------------------------------------------------------
TRUNCATE TABLE empties a table without logging row deletions in the
transaction log. It can't be used with
tables referenced by FOREIGN KEY constraints, and it invalidates the
transaction log for the entire database.
Once the transaction log has been invalidated, it can't be backed up
until the next full database backup.
------------------------------------------------------------

Does it mean that the log backup taken after table truncation is an
invalid backup?

Help me! i'm lost....

Thanks in advance
Ronin

Hi Ronin,

It's not invalid - but it's not useful anymore either.

If you use the full recovery model, a restore to a point in time
consists of two steps:
1. Restore a full backup to restore the database to the state it had
when that full backup was taken;
2. Restore one or more log backups to re-apply all changes that have
been made to the database after the full backup was taken, up to the
point in time where you want the restore to stop.

A TRUNCATE TABLE operation is minimally logged. That means that there is
just enough information in the log file to rollback if the transaction
fails or commit if it doesn't - but not enough to re-apply the changes.

Now, if one change can't be re-applied, you'll agree that it makes no
sense to go on re-applying changes that were done after that change. In
fact, it is very dangerous, since it might ruin your databases data
integrity.

As a result, when you execute TRUNCATE TABLE or other minimally-logged
operations, the effect will be that you can't restore to a point-in-time
between the moment the TRUNCATE TABLE was executed and the next full
backup.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

A TRUNCATE TABLE operation is minimally logged. That means that there is
just enough information in the log file to rollback if the transaction
fails or commit if it doesn't - but not enough to re-apply the changes.

Reference?

TRUNCATE TABLE logs the extent deallocations, and that is perfecly
enough to reapply the changes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Sun, 20 Nov 2005 19:54:45 +0000 (UTC), Erland Sommarskog wrote:

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

A TRUNCATE TABLE operation is minimally logged. That means that there is
just enough information in the log file to rollback if the transaction
fails or commit if it doesn't - but not enough to re-apply the changes.

Reference?

TRUNCATE TABLE logs the extent deallocations, and that is perfecly
enough to reapply the changes.

Hi Erland,

mmmm.... 'kay, I guess you're right.

But then, what DOES the quote that Ronin posted mean?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Hugo Kornelis]

On Sun, 20 Nov 2005 21:10:17 +0000 (UTC), Erland Sommarskog wrote:

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.

Hi Erland,

Ah, okay. Learned something new today! <g>

Thanks for the explanation!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)