Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partition Splitting Problem<Changed> Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 11:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 6, 2014 11:35 PM
Points: 75, Visits: 262
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
Posted Wednesday, January 16, 2013 7:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504

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
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407829
Posted Wednesday, January 16, 2013 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 6, 2014 11:35 PM
Points: 75, Visits: 262
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 »

Add to briefcase

Permissions Expand / Collapse