Partition Splitting Problem<Changed>

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 Splitting Problem<Changed>

Partition Splitting Problem<Changed>

Rate Topic

Display Mode

Topic Options

Author

Message

swaroop.sahoo

Posted Tuesday, January 15, 2013 11:47 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 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 get 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.

MY 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)

Masters.....CAN ANYBODY HELP???

-Swaroop

Post #1407624

Want a cool Sig

Posted Wednesday, January 16, 2013 7:19 AM

SSC Veteran

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

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)

Your NEXT USED is suppose to specify the filegroup and not the actual file.

ALTER DATABASE DBName
ADD FILEGROUP FG_TBL_201301;
GO
ALTER DATABASE DBName
ADD FILE 
(NAME =F_TBL_201301,
FILENAME ='C:\F_TBL_201301.ndf',
SIZE = 50MB,    
MAXSIZE = unlimited,    
FILEGROWTH = 10%
) TO FILEGROUP FG_TBL_201301;
GO
ALTER PARTITION SCHEME PS_TBL NEXT USED [FG_TBL_201301];
GO
ALTER PARTITION FUNCTION PF_MSCRaw() SPLIT RANGE (20130131)

---------------------------------------------------------------
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 #1407829

swaroop.sahoo

Posted Wednesday, January 16, 2013 7:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:36 AM
Points: 75, Visits: 255

I create one file group for each file. Actually that kind of issue will give you error right away. My script ran for more than a day without any output. Now the problem has become more critical. I stopped the script and the rollback is running more than 12 hours.

I am DEAD...

Post #1407834

« Prev Topic | Next Topic »

Permissions