Partition Switching Problem

Question

Post reply

Partition Switching Problem

swaroop.sahoo

SSChasing Mays

Points: 617
More actions
January 15, 2013 at 8:52 am

#266988

Hi All,
I have monthly partition (Size 150 GB per month). I forgot to create partition for January-2013. So all the January Data went to the dummy partition I created for the last range. As microsoft recomends the last partition should be empty.
I tried to create a partition for January and switch the data in the dummy partition to the newly created partition. But the script ran for more than a day without any data transfer.
I am not sure how to create new partitions for the table now. The server is having 32 cores and 128GB memory. So I don't think transfering 50GB data (till Jan 12th) witll be a problem for it.
Masters.....CAN ANYBODY HELP???
-Swaroop

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

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

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 1

Can you provide scripts for the partition scheme and function.

I think what you're wanting to do is a split and not a switch but I can be wrong here.

I've forgotten to create partitions many times and fixing it isn't a big problem.

i.e.

create new filegroup. modify partition function to set Next Used filegroup. alter p.scheme to split jan2013

it'll move the data when it splits.

http://technet.microsoft.com/en-us/library/ms186307(v=SQL.100).aspx

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

swaroop.sahoo SSChasing Mays Points: 617 More actions · Answer 2

Hi,

THanks for the reply. You correct I am trying to do a split of range. But the problem I am facing is SQL server is unable to move the data fro the last dummy partition to the new partition. I am using SQL Server 2008 R2. Here is the script:

ALTER DATABASE DBName ADD FILE

(NAME =F_TBL_201301,FILENAME ="<location>\F_TBL_201301.ndf",

SIZE = 50MB, MAXSIZE = unlimited, FILEGROWTH = 10%

) TO FILEGROUP FG_TBL

ALTER PARTITION SCHEME PS_TBL NEXT USED [F_TBL_201301];

ALTER PARTITION FUNCTION PF_MSCRaw() SPLIT RANGE (20130131)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

swaroop.sahoo (1/15/2013)
I tried to create a partition for January and switch the data in the dummy partition to the newly created partition. But the script ran for more than a day without any data transfer.

HOw did you try the transfer the data , have you tried the switch ?

and one more thing your dummy table (where you had transferred the data temporarily) placed on partition scheme?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

swaroop.sahoo SSChasing Mays Points: 617 More actions · Answer 4

Hi Bhuvnesh,

Thanks for the reply.I didn't specify any range for January data. So it went to the last partition. Ideally, if I create a partition for january.. the data in the last partition should move to January partition.

The data is not is not in a dummy table . It is in the last partition.

-Swaroop