7

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.

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

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

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

BEGIN TRANSACTION;
  EXEC sys.sp_rename N'dbo.OldTable', N'garbage',  N'OBJECT';
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
COMMIT TRANSACTION;
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.

5
  • 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 '15 at 14:45
  • Should not take long at all. – Aaron Bertrand Aug 17 '15 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 '15 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. – Aaron Bertrand Aug 17 '15 at 15:18
  • Perhaps consider a truncate first? might reduce the drop time (just a thought) – War Apr 28 '17 at 17:01

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.