Adding new filegroup to an existing partition scheme

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 3, 2014 at 2:59 pm

  #298024

  I have a situation where I need to modify a partition scheme to include a new filegroup.

  Here's the sample DDL I put together

  --------------------------------------------------------------------------------

  -- 1. Drop/Recreate:

  --------------------------------------------------------------------------------

  CREATE DATABASE temp2;

  GO

  USE temp2

  GO

  --------------------------------------------------------------------------------

  -- 2. Add filegroups

  --------------------------------------------------------------------------------

  ALTER DATABASE temp2 ADD FILEGROUP tp2fg1;

  ALTER DATABASE temp2 ADD FILEGROUP tp2fg2;

  ALTER DATABASE temp2 ADD FILEGROUP tp2fg3;

  ALTER DATABASE temp2 ADD FILEGROUP tp2fg4;

  ALTER DATABASE temp2 ADD FILEGROUP tp2fg5;

  GO

  --------------------------------------------------------------------------------

  --3. Create the files and apply to the file groups

  --------------------------------------------------------------------------------

  ;-- fg1

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat1,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat1.ndf',

  SIZE = 15MB,

  MAXSIZE = 50MB,

  FILEGROWTH = 5MB

  )

  TO FILEGROUP tp2fg1;

  GO

  ;-- fg2

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat2,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat2.ndf',

  SIZE = 15MB,

  MAXSIZE = 50MB,

  FILEGROWTH = 5MB

  )

  TO FILEGROUP tp2fg2;

  GO

  ;-- fg3

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat3,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat3.ndf',

  SIZE = 15MB,

  MAXSIZE = 50MB,

  FILEGROWTH = 5MB

  )

  TO FILEGROUP tp2fg3;

  GO

  ;-- fg4

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat4,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat4.ndf',

  SIZE = 50MB,

  MAXSIZE = 200MB,

  FILEGROWTH = 5MB

  )

  TO FILEGROUP tp2fg4;

  GO

  ;-- fg5

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat5,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat5.ndf',

  SIZE = 50MB,

  MAXSIZE = 500MB,

  FILEGROWTH = 50MB

  )

  TO FILEGROUP tp2fg5;

  GO

  --------------------------------------------------------------------------------

  -- 4. Create the partition function

  --------------------------------------------------------------------------------

  CREATE PARTITION FUNCTION testpartition (int)

  AS RANGE LEFT FOR VALUES

  (

  250000,-- fg1 0-250000

  500000,-- fg2 250001-500000

  750000,-- fg3 500001-750000

  1000000-- fg4 750001-1000000

  -- fg5 1000001+

  );

  GO

  --------------------------------------------------------------------------------

  -- 5.Create the partition scheme

  --------------------------------------------------------------------------------

  CREATE PARTITION SCHEME testscheme

  AS PARTITION testpartition

  TO (tp2fg1, tp2fg2, tp2fg3, tp2fg4, tp2fg5);

  GO

  --------------------------------------------------------------------------------

  -- 6.Create the table and assign it to the partition scheme

  --------------------------------------------------------------------------------

  CREATE TABLE dbo.TestPartTable

  (

  col1 int identity primary key,

  col2 varchar(100) not null

  )

  ON testscheme(col1);

  GO

  As you can see, the last filegroup is tp2fg5. What I want to do is change tp2fg5 to take the values 1,000,001-1,250,000 and add a 6th file group (tp2fg6) for the values 1,250,000+. In other words, I want to ALTER my partition function to look like this:

  CREATE PARTITION FUNCTION testpartition (int)

  AS RANGE LEFT FOR VALUES

  (

  250000,-- fg1 0-250,000

  500000,-- fg2 250,001-500,000

  750000,-- fg3 500,001-750,000

  1000000,-- fg4 750,001-1,000,000

  1250000-- fg5 1,000,001-1,250,000

  -- fg6 1,250,001+

  );

  GO

  Currently tp2fg5 has millions of records beginning with the values: 1,000,000+.

  I don't think MERGE or SPLIT will help me.

  I know I could export the data, re-create everything (my partition function and partition scheme) with the the settings I want and then pull the data back into my table with the new partition function/scheme. Is there a better way?

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 4, 2014 at 10:26 am

  #1756591

  I figured it out.

  Using the DDL above I would...

  -- (1) Add new file group

  ALTER DATABASE temp2

  ADD FILEGROUP tp2fg6;

  GO

  -- (2) Create new file for the filegroup

  ALTER DATABASE temp2

  ADD FILE

  (

  NAME = tp2dat6,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014ENT\MSSQL\DATA\tp2dat6.ndf',

  SIZE = 15MB,

  MAXSIZE = 50MB,

  FILEGROWTH = 5MB

  )

  TO FILEGROUP tp2fg6;

  GO

  -- (3) Update the partition scheme

  ALTER PARTITION SCHEME testscheme

  NEXT USED tp2fg6

  -- (4) Modify the parition function to include a new range of values

  ALTER PARTITION FUNCTION testpartition()

  SPLIT RANGE (1250000)

  --(

  -- now we have

  --250000,-- fg1 0-250000

  --500000,-- fg2 250001-500000

  --750000,-- fg3 500001-750000

  --1000000-- fg4 750001-1000000

  ---- fg5 1000001+

  --);

  GO

  Partitioning is still a bit new to me.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001