Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file group that I am going to be removing. I also create all the indexes on the empty table so it matches exactly the data table on that partition.

Once the empty table and indexes are created, I issue the command

alter table mydatatable switch partition 2 to emptytable

The command takes forever to run and after reading everything I could about the switching, I thought it was only moving meta data over and a very quick step.

My plan is to swap the data to the empty one and then truncate the empty table and remove the file group.

To add a little more details. The data table started off as a non-partitioned table. Due to the number of r5ecords in it, we had to partition it. So I created the partition schema, function and all of the file groups. I dropped the clustered index and all other indexes and recreated them all. The partition process looks to be successful.

When I run the alter table command, it feels like there is data being transferred and not just meta data.

share|improve this question
Locking issue. sp_who2. Who is blocking your switch? – usr Aug 4 '14 at 23:19
sp_WhoIsActive is far better than sp_who2 :-) – Kin Aug 5 '14 at 3:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.