wodom (10/7/2009)
In practice, even with a power fail in the middle, you're not going to end up with pages that are marked as allocated but are not allocated, etc. All that stuff would be rolled back and made consistent when you start up again.
Absolutely. That's why every operation in SQL is logged to one extent or another, so that there's no chance of leaving the database in an inconsistent state due to an unexpected shutdown.
That's why, if it was possible to delete/drop/truncate without any logging at all (which it's not), it wouldn't be possible to just start over after an an expected shutdown. With the way SQL does changes in memory/disk it would be possible to end up with a huge mess. Pages allocated to two objects, pages marked as allocated but not allocated, etc.
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