Re: Log Invalidated after truncate table

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)
.

Relevant Pages

  • Re: Exchange realtime backup solution
    ... With typical Exchange-aware backup solutions you can restore up to point of failure. ... The whole transaction logging feature of Exchange database engine was created just for that - you have data in database and copy in transaction logs. ...
    (microsoft.public.exchange.admin)
  • Re: Did backing up the tail create another transaction log?
    ... The tail of the log contains the transactions since the last log backup. ... Full Restore database with NoRecovery ... RESTORE HEADERONLY shows that there are only 3 transaction logs. ...
    (microsoft.public.sqlserver.server)
  • Re: Restore of Transaction
    ... "Leave database nonoperational but able to restore additional transaction ... When you perform a backup of the database, the backup backs up the database ...
    (microsoft.public.sqlserver.server)
  • Re: Difference Between Truncate and Delete
    ... sequence of log backups, if the log includes a lot of DELETEs, those DELETEs ... TRUNCATE will be redone, so that the restore will be much faster. ... >> TRUNCATE can be inside a transaction, and if the transaction is rolled ...
    (microsoft.public.sqlserver.programming)
  • Re: Transaction log truncation on full backup
    ... As Uri posted, full backup does not truncate the log, this is easy to test. ... SQL Server is smart enough to not replay the same transaction twice... ...
    (microsoft.public.sqlserver.server)