October 28, 2008 at 7:23 am
Can we dettach files from the partition where there are 5 file groups in which each has 1 year of data consisting of millions of records and total of 5 yrs data. I just need to have only 5 year of data and get rid of old data as and when i get new year data.
What wud be the best approach in partitioned files groups.
October 28, 2008 at 8:09 am
Assuming one table with five partitions (data for one year in each partition), with the data of each partition stored in its own file group, and assuming you've just received the "next" year's data and you want to drop the oldest year's data and replace it with the new year's data, I would:
- Create an exact copy of the table -- same columns, data types, indexes, AND based on the same partitioning scheme. Configure this table to have its data stored in the same file group as that the of the data you want to drop.
- Use partition switching ("ALTER TABLE ... switch partition ...") to switch the data of that partition from the original table into this dummy table.
- Truncate the dummy table
Depending, you could then load the new data into the dummy table, validate it, and then switch it back into the "live" table. A more complex solution might be to set up a sixth partition, load the data into a dummy table based in that partition, and then when everything's set to go sitch old out, then new in... resulting in only a few seconds where there's less than five years of data available. Much depends on your operational requirements.
Philip
October 28, 2008 at 12:02 pm
You mean create adummy table with same schema and add 4 flies and 1 new file to that table and then truncate original table.
but i do no have enough space for dummy table as it has very huge data in it.
If am getting you wrong am sorry, can you please put that in sql format. I mean what sql statements we'll be using after creating a dummy table structure.
October 28, 2008 at 12:35 pm
You only need to create a shell of the table, i.e. just columns and data types. What Phillip is describing is called "SWITCHING" out data. You "move" data from one partition in one table to an EMPTY partition in another table then you truncate the copy. However, in order to do this the second table MUST 1) have the same structure (columns and data types) and 2) be partitioned on the same file groups.
November 5, 2008 at 12:12 pm
Configure this table to have its data stored in the same file group as that the of the data you want to drop
Philip
can you pls elaborate the above statement from your. Iam not sure how i have do this configuration part.
thanks
November 5, 2008 at 1:04 pm
did you check Kimberly's very good white paper on partitioning ?
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
BOL also has some info on sliding windows:
- "Transferring Data Efficiently by Using Partition Switching"
- "Designing Partitions to Manage Subsets of Data"
and a nice example at
http://www.sqlservercentral.com/Forums/Topic417556-146-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2008 at 8:45 pm
Can you post a CREATE TABLE script for the table that you are working with? It'd be easier to show examples with something you are familiar with.
Philip
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply