1

Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?

5

No, the transaction log behaves and performs the same way in all recovery models, the only difference is when log truncation occurs.

The MSDN article on the transaction log provides detail on this.

  • This answer implies all operations when it should explicitly state it only applies to Updates and Deletes. Inserts will never be minimally logged in the Full recovery model but can be in the Bulk logged or Simple models. technet.microsoft.com/en-us/library/… – brian Dec 19 '13 at 21:38
  • @brian Minimally logged operations can only occur under very specific conditions and even then may not have a significant impact. – Mike Fal Dec 20 '13 at 16:14
  • 2
    The question is if the performance of a delete would be negatively affected by changing from the simple to full recovery model. I think @mikefal his answer is spot on +1. However, regarding minimally logged insert not having significant impact I don't agree. It can have a massive possitive effect on performance.. – Edward Dortland Dec 20 '13 at 19:42
  • 1
    @MikeFal I wouldn't have made the comment had you not made such a broad statement. I do agree that it's only specific conditions, but good ETL developers can spout them off without hesitation. I've seen a few tweaks to enable minimal logging cut hours off of processing time. Great for ETL and batch processing environments were time is money. – brian Dec 23 '13 at 20:58
  • 1
    @brian It's a fair point and I appreciate you leaving the comment as a point of clarification. No worries. :) – Mike Fal Dec 23 '13 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.