How to truncate data in one particular file group.

Question

How to truncate data in one particular file group.

Ron007

Mr or Mrs. 500

Points: 564
More actions
February 11, 2014 at 9:33 am

#303964

How to truncate data in one particular file group.
we are maintaining data in flie groups (using partition function and schema )
large 3 tables data storing in one file group1 based on 2011
2012 data in file group 2
2013 data in file group 3
.. I would like to delete data completely in file group 1. how?? any idea.
if delete data in 3 tables using date parameter., it takes hours and hours hence would like to delete whole data in file group1.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

You could switch out the partition with an empty table (I forget the command offhand), then truncate the swapped out table.

edit: I think it is ALTER TABLE SWITCH PARTITION.

Ron007 Mr or Mrs. 500 Points: 564 More actions · Answer 2

Thanks for your reply. I think we can move/transfer data by Using switch command. my problem is .. delete complete data in one file group. If i lose data . No problem. (becuase its storing historical data) . We dont need it. we need space.

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 3

alter table switch partition effectively updates the metadata to switch the definitions of the partition and the table. You can truncate the dummy table after the switch, and have your space back. Effectively, you move/switch an empty table into the partition. Give it a try on a test system.