create new partition file groups on table

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  April 9, 2017 at 2:40 pm

  #322862

  hello all,

  I have a table that has 2.682 trillion rows...I know...last week I had to figure out how to remove the old files, 2014, from the table so I could create new ones this week. I created a new filegroup inv_201605. I created a new inv201605.ndf file to move the data into and associated the ndf file with the new filegroup. I have read several articles. I ran the next used command then tried running the split but that lasted for over a day. below are the queries. I did create a temp table at one point and move over the default file, ran the below and a rebuild but that also last a whole day and grew the file to an outrageous amount, I thought this can't be right it's rebuilding the default into the new filegroup when I only want 05012016 to 05312016.

  Please help me if you can.

  ALTER PARTITION SCHEME psch_Inv

  NEXT USED [INV_201605];

  ALTER PARTITION FUNCTION pfn_Inv()

  SPLIT RANGE ('2016-05-31');
  

  CREATE

  UNIQUE CLUSTERED INDEX PK_tblInventory_NewerToOlderTemp

  ON dbo.tblInventory_NewerToOlderTemp(Transdate, StoreID, Productid)

  WITH(DROP_EXISTING=ON)

  ON [INV_201605];

  MCSE SQL Server 2012\2014\2016