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?

1 Answer 1

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.

5
  • 1
    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
    Commented Dec 19, 2013 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
    Commented Dec 20, 2013 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.. Commented Dec 20, 2013 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
    Commented Dec 23, 2013 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
    Commented Dec 23, 2013 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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