wodom (10/5/2009)
Steve Jones - Editor (10/5/2009)
It's easy to say I want to do this, but think about the times where you have an issue. What if you get halfway through and power dies?If power dies, or other issue, no problem. Just start over with either the TRUNCATE or DROP, whichever is being used.
It's far from that simple. In a truncate, what's logged is the page deallocations and the resulting changes to the allocation pages.
If you could run it without any logging and there's a crash half way through you can end up with pages that are marked as allocated but are not allocated. It's not just data changes that are logged, it's changes to the system tables, changes to the allocation pages, database structure changes, etc...
Edit: Clarified that I was talking about the hypothetical case of delete/truncate without logging at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability