August 19, 2004 at 1:39 pm
Is truncate bad when the table doesn't need to be backed up? Does it break log shipping? Does it prevent backups from being restored?
Under what circumstances this should be allowed (if any)?
August 19, 2004 at 1:46 pm
"The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed."
"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger."
"TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead."
The table should be truncated too in destination once log has been restored in logshipping. There is no relation between truncate table and database/log restoration.
August 19, 2004 at 2:09 pm
"TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead."
This is actually incorrect. The data still exists it just deallocates as you said before the pointer to the data in sysindexes which is logged. All the data remains until overwritten or cleaned up in some other way.
August 20, 2004 at 1:30 am
If you want to remove all the data from a table, use TRUNCATE TABLE because its damn fast. The transaction log will just contain the TRUNCATE TABLE command.
If you use DELETE FROM then the transaction log contains a record for EVERY ROW in the table, a slower process.
Julian Kuiters
juliankuiters.id.au
September 1, 2004 at 2:03 am
i think you have to shrink DB after making your Truncate .. or shrink Log after making delete .. so the DB size is less and the space returned to Operating System (Disk)
is this correct ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
September 1, 2004 at 3:35 am
True. TRUNCATE TABLE does not shrink the size of the physcial database files on disk. It also doesn't cause your transaction log to unnecessarily grow like a DELETE Table will.
There are a few gotcha's with TRUNCATE TABLE, namely that it resets the identity counter, and can't be used with tables that are referenced with foreign key constraints.
From BOL (TRUNCATE TABLE):
"The counter used by an identity for new rows is reset to the seed for the column"
...
"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint"
...
"Because TRUNCATE TABLE is not logged, it cannot activate a trigger"
Julian Kuiters
juliankuiters.id.au
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply