Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 at 23:19
    
sp_WhoIsActive is far better than sp_who2 :-) –  Kin Aug 5 at 3:23

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.