Dropping a large table

Question

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.

Answer 1

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.