On our production db there is a table 180 GB, around 2 millions records, I want to re-create it as a new empty table in a new filegroup and delete the existing table. What is the best practice to perform this task without affecting the DB performance?

My time concern will be in renaming the table and dropping it.

I want to add this is a very high load DB so I don't want to get any locks or long running queries.


1 Answer 1

  1. Create the new table
  2. Drop the old table and rename the new one in a transaction:

      DROP TABLE dbo.OldTable;
      EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';

If you have concerns about how long the drop itself will take, you can do it this way instead:

  EXEC sys.sp_rename N'dbo.OldTable', N'garbage',  N'OBJECT';
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
DROP TABLE dbo.garbage;

(The drop doesn't technically have to be a part of the transaction, and shouldn't cause any blocking in any case, so now how long the drop actually takes is largely irrelevant, because there is no chance for it to block anyone.)

If you have indexes, foreign keys etc. you'll also have to deal with those, but the above is about the fastest you're going to get because it's mostly metadata (at worst the DROP will be blocked by existing activity).

Also see Schema Switch-A-Roo part 1 and part 2 if your situation is more complex.

  • Thanks @aaronbertrand. my bad i meant drop , i will Edit the post. do you have a rough estimate how long it should take to drop a table with this size
    – sebeid
    Aug 17, 2015 at 14:45
  • Should not take long at all. Aug 17, 2015 at 15:08
  • 1
    Isn't it necessary to add error handling to that rename transaction? I think if the first rename fails the second one will still be executed (which is stupid). I like XACT_ABORT ON for this.
    – usr
    Aug 17, 2015 at 15:13
  • @usr I'm not sure I know of a situation where if that first rename were to fail, the second wouldn't fail too, even if the failure didn't abort the batch. Quite simple logic actually: if the first rename fails, the second rename can't possibly succeed (I don't think this is "stupid"). Do you have a specific situation in mind? A repro even? I don't see the benefit of littering such simple code with try/catch noise, especially for a one-off operation, and especially since the end result will only differ in that a single sp_rename won't be attempted. Aug 17, 2015 at 15:18
  • Perhaps consider a truncate first? might reduce the drop time (just a thought)
    – War
    Apr 28, 2017 at 17:01

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.