Is Truncate Table logged or not??

[stevenippon]

I've had a long running argument with a colleague about truncate table. Is it (a) logged (b) minimally logged or (c) non-logged.

I said it was (b) as i thought you could truncate table and still recover using transaction log dumps, but he reckoned it would screw up transaction dump (like select into perm./ table).

Help would be greatly appreciated!!

 

[dickiebird]

Hmm... Make of this what you will !

From Sybase SQL Server™ Transact-SQL® User’s Guide
Rules Associated with Triggers:

You cannot create a trigger on a view or on a temporary table,
though triggers can reference views or temporary tables.
Although a truncate table statement is, in effect, like a delete without
a where clause because it removes all rows, it cannot fire a
trigger because individual row deletions are not logged.

Some commands that change the database are not logged, such as
truncate table, bulk copy into a table that has no indexes, select into,
writetext and dump transaction with no_log.

Use truncate table as a fast method of deleting all the rows in a table. It’s
almost always faster than a delete statement with no conditions,
because the delete logs each change, while truncate table just logs the
deallocation of whole data pages. truncate table immediately frees all
the space that the table’s data and indexes had occupied. The freed
space can then be used by any object.

And...... from Adaptive Server® Anywhere SQL Reference Manual
The TRUNCATE TABLE statement is entered into the transaction log as a
single statement, like data definition statements. Each deleted row is not
entered into the transaction log.

HTH
;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[JMCraig]

This should be pretty easy to test. Typical non-logged operations (like SELECT INTO a permanent table or a "fast" bulk copy) prevent you from actually dumping the transaction log. So, you could easily test this in a development DB: enable transaction logging if it isn't already, truncate a table, attempt to dump the log. Now enable SELECT INTO/BULK COPY and do a SELECT INTO a permanent table; attempt to dump the log.

My guess is that it'll work in the first case, but not in the second case. Your calling it "minimally logged" accords with my understanding of what it tracks (just the deallocation of the table's pages).

BOL,

John
J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net

 

[stevenippon]

thanks to both of you for reply. i'll give it a go in test environment. (after doing websearches for this, i found all kinds of answers!)