3

Have two semi large related tables (284,6876,609 rows - 156,785,176 KB and 4,2628,162 rows - 2,212,464 KB) in a log shipped db that needs to be either purged or dropped. How can I dispose of these two tables while minimizing impact to log shipping?

Improve this question
2
  • 2
    OT: I didn't realise this group was about databases, and I genuinely thought about someone having to dispose large wooden tables by shipping them somewhere as if they were logs.
    – o0'.
    Jan 1, 2021 at 10:18
  • Please check this answer also for thorough understanding - dba.stackexchange.com/a/270689/136766
    – Learning_DBAdmin
    Jan 1, 2021 at 17:32

1 Answer 1

Reset to default
11

If you DROP the tables, it'll cause minimal logs to be generated because it doesn't immediately force garbage collection to occur on the used space, it merely basically just removes the reference. You similarly can accomplish the same outcome with TRUNCATE (though I think on large tables there could be more overhead than DROP, I'll have to research this and update my answer accordingly).

This is a really good article that details why TRUNCATE and DROP operations are seemlessly quick and generate minimal logs, which is due to a feature of SQL Server called deferred drop.

Either way, avoid DELETE otherwise that will generate a ton of logs that will need to get shipped and reapplied at the target database.

This StackOverflow Question has multiple answers with good information about efficiently removing data from a large table (some maybe not specificly related to your question, but good for future reference or other users with similar problems).

Improve this answer
4
  • 1
    I ran a local test of "delete from table" vs "drop table" vs "truncate table". "Delete from table" generated the largest transaction log backup while "truncate table" generated the smallest transaction log backup.
    – user2368632
    Dec 31, 2020 at 18:24
  • @user2368632 Interesting, good to know. I thought I've read something about TRUNCATING potentially having other overhead that DROP doesn't have (outside of log generation) but I'll have to dig back and will update my answer accordingly. But yes best way to really know is testing when possible.
    – J.D.
    Dec 31, 2020 at 19:04
  • 1
    @J.D. - Possibly, you are looking for this answer - dba.stackexchange.com/a/270689/136766
    – Learning_DBAdmin
    Jan 1, 2021 at 17:33
  • 1
    @Learning_DBAdmin Yes! Thank you! There's some good information in Josh's answer with what overhead there can be with TRUNCATE (though still a better alternative to DELETE).
    – J.D.
    Jan 1, 2021 at 17:58

Your Answer

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

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