October 18, 2011 at 12:01 pm
Hey all,
I am having an issue with partitioned tables and looking for a solution.
DB: over 2TB
DB Recovery option: Simple recovery mode
Partition range: MonthID
I have accidently added next month data without updating the partition function. So, the data was added to primary filegroup instead of next filegroup. So if i update the function and split the data now then the sql server will have to go through all data including other filegroups to split data to new filegroup. As i mentioned the DB is 2TB and it will take forever and dont have enough space for log file as it will grow to over 2TB during the split.
Is there any quick way for me to split this data from Primary file to another filegroup?
Thanks
October 18, 2011 at 1:49 pm
Any reason why you can't swap in a empty partition for the one that needs split, split the partition, then copy data back into it?
You're going to have to eat the cost of moving the data from the primary filegroup to the correct file group in any case.
October 18, 2011 at 1:57 pm
I understand that the database is 2GB, but not the data for the month right? That is only the size of the data that has been entered since. My guess is that you will not have a problem.
Thanks,
Jared
Jared
CE - Microsoft
October 18, 2011 at 2:09 pm
The actual data will be 350 GB for that month.. I am not only spliting the range, i need to also move the data from primary group to another filegroup.
swap in to another scheme and then split range is easy but transferring that data to diff filegroup is next challange.
So you switch out the month( 350 GB) data to nonpartitioned tables which will allow you to split the range in a sec. After the split, you move data back in but then again the data will be still on Primary group and I need to move them to diff filegroup. So I want to split the range and move the data to diff filegroup.
October 18, 2011 at 2:33 pm
Copy the data to a new table, delete it from the primary, re-build your partition function/scheme, and then re-include the data. That's really the only way to avoid the problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2011 at 3:00 pm
Are you sure it went into the primary filegroup? Why is primary filegroup even a part of your partition schema? i.e. I have 12 filegroups other than primary, 1 for each month. Right now, if any data gets inserted into the table after October and I have not yet slid the window... It will still be part of October's filegroup. So, do you not have the last section of your range assigned to its own filegroup?
Thanks,
Jared
Jared
CE - Microsoft
October 19, 2011 at 9:36 am
Yes, i have filegroup for each month. I was forgot to modify the scheme to point next range to filegroup so by default it went to Primary.
Here is my take on this since i have to make this data available all the time so i can't drop it from primary.
I need to create 2 sets of non-partitioned tables where one will sit on Primary group and other will be on SEPT filegroup
1) copy the month data to non-partitioned tables on SEPT filegroup
2) switch out the data to non-partitioned tables on Primary group
3) Split the range (Update scheme and function)
4) Switch in data from SEPT filegroup
5) drop non-partitioned tables on Primary group
This way i will have data available at any time since users run report constantly. THe Only time data wont be available when i do switch out/in which will take couple of sec in this process.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply