File Groups under primary

• Arjun SreeVastsva

  SSCertifiable

  Points: 7137

  More actions

  October 9, 2017 at 1:35 pm

  #402584

  Hi,

  i have created nearly 30 file groups .but later on i found all file groups under primary group only.
  how can split into secondary file groups.

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  October 9, 2017 at 2:48 pm

  #1962904

  You mean you created 30 filegroups and then created all your data files under primary? Or did you not actually create the filegroups?

  Adding filegroups and then creating files IN those groups should follow along the lines of below. If you created a bunch of files under primary, you're not going to be able to move those into secondary filegroups. You'll have to drop the files and recreate them in their appropriate groups.

  ALTER DATABASE FilegroupTest ADD FILEGROUP SecondaryGroup1
ALTER DATABASE FilegroupTest ADD FILEGROUP SecondaryGroup2
ALTER DATABASE FilegroupTest ADD FILEGROUP SecondaryGroup3
ALTER DATABASE FilegroupTest ADD FILEGROUP SecondaryGroup4

  ALTER DATABASE FilegroupTest ADD FILE
(
NAME = Group1File1,
FILENAME = 'c:\mssql\Group1File1.ndf',
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROWTH = 1
)
TO FILEGROUP SecondaryGroup1;

  ALTER DATABASE FilegroupTest ADD FILE
(
NAME = Group2File1,
FILENAME = 'c:\mssql\Group2File1.ndf',
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROWTH = 1
)
TO FILEGROUP SecondaryGroup2;

  ALTER DATABASE FilegroupTest ADD FILE
(
NAME = Group3File1,
FILENAME = 'c:\mssql\Group3File1.ndf',
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROWTH = 1
)
TO FILEGROUP SecondaryGroup3;

  ALTER DATABASE FilegroupTest ADD FILE
(
NAME = Group4File1,
FILENAME = 'c:\mssql\Group4File1.ndf',
SIZE = 10MB,
MAXSIZE = 20MB,
FILEGROWTH = 1
)
TO FILEGROUP SecondaryGroup4; '

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734552

  More actions

  October 10, 2017 at 8:54 am

  #1963029

  Please script the code for the creation of your database and post it.