Partition Switching Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Partition Switching Problem

Partition Switching Problem

Rate Topic

Display Mode

Topic Options

Author

Message

swaroop.sahoo

Posted Tuesday, January 15, 2013 8:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:36 AM
Points: 75, Visits: 255

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

Post #1407309

Want a cool Sig

Posted Tuesday, January 15, 2013 11:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221, Visits: 452

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 - Future MCM 2025 Smile

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 Cool

I want to win the lotto BigGrin

I want a gf like Tiffa w00t

Post #1407394

swaroop.sahoo

Posted Tuesday, January 15, 2013 11:49 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:36 AM
Points: 75, Visits: 255

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)

Post #1407626

Bhuvnesh

Posted Wednesday, January 16, 2013 3:27 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1407721

swaroop.sahoo

Posted Wednesday, January 16, 2013 3:33 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:36 AM
Points: 75, Visits: 255

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

Post #1407723

« Prev Topic | Next Topic »

Permissions