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?

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 at 10:18
  • Please check this answer also for thorough understanding - dba.stackexchange.com/a/270689/136766 – Learning_DBAdmin Jan 1 at 17:32
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).

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 '20 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 '20 at 19:04
  • 1
    @J.D. - Possibly, you are looking for this answer - dba.stackexchange.com/a/270689/136766 – Learning_DBAdmin Jan 1 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 at 17:58

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.