GilaMonster (3/8/2011)
Koen Verbeeck (3/8/2011)
If a data load fails, the destination table can be truncated and the load can start over again, so you would not have to worry about inconsistency.What would happen if the server failed at the point SQL was modifying the allocation constructs or system tables in the database and there was no logging? Not so easy to fix.
Logging is not just for the user data. It's for page allocations, allocation page modifications, system table modifications and a while lot more.
I had this argument with someone recently about loads. They were assuming that the load would fail and they'd restart it, but you need logic to allow restarts. Not everyone builds this into their processes and even if you don't have restart logic, the cleanup of old data needs to be transactional. So have you saved anything if we allowed imports w/o logging? Not sure, and honestly, not sure the vast majority of people are qualified to decide this.
Even if you are, is that the best decision for the company? The next person that does your job might not understand this type of feature and use it in other places.
If you don't need transactional logging during imports, then commit periodically, inserting batches of 10,000 or so, and running some log backups. If you're that busy and you have that much data, then I assume you ought to have some money to put log backups on separate spindles, and the t-log on separate spindles, and get better performance.
Ultimately, I don't think it's worth the risk to relational data to allow this. If the data isn't that important, or can be reloaded easily, perhaps it would be better to put this into some other structures. Maybe a NoSQL or columnar construct.