(This was originally a comment to @DaveE's answer, but I've put it into its own answer because it got long)
TRUNCATE is a logged operation. It has to be otherwise it's not ACID-compliant. However, differences between
- Log space usage:
TRUNCATE only logs pages/extents* freed, whereas
DELETE logs individual rows.
- Lock usage:
TRUNCATE will generally use less locks, since it takes a table lock and page locks, as opposed to
DELETE which uses row locks**.
TRUNCATE resets the identity sequence on a table, if present.
(* An extent = 8 pages.
TRUNCATE will log/remove extents if they're all from that one table, otherwise it'll log/remove pages from mixed extents.
** One side effect of this is that
DELETE FROM TABLE can potentially leave empty pages allocated to the table, depending on whether the operation can get an exclusive table lock or not.)
So (back to the original question),
TRUNCATE TABLE is conclusively better than
DELETE FROM TABLE if you're emptying the table out but want to keep the structure (NB:
TRUNCATE can't be used on a table that's referenced by a foreign key from another table).
As noted in @Tullo's comment, also check your database's recovery model - if it's full, then you either need to start taking log backups, or change your recovery model to simple. Once you've done either of those, you'll probably want to shrink your log file as a once-off operation (NB: log file only) in order to reclaim all that free space.
Finally, another thing to be aware of - table statistics. run
UPDATE STATISTICS <TABLENAME>' afterTRUNCATE
/DELETE` so the query optimiser doesn't get tripped up by old statistics.