locked
SQL 2005 Filegroup RRS feed

  • Question

  • Hi,

    Created new file group in sql 2005(Standard Edition) and moved one table using Management studio.

    My doubt is after moving what about ,Primary Kay,Indexes and Constraints on that table. Will it properly work after moving.

    or anything we have to do before moving to file group.?

     


    • Edited by VijayKSQL Wednesday, December 7, 2011 2:07 PM typo
    Wednesday, December 7, 2011 2:03 PM

Answers

  • Getting this error: - Unable to modify table. 
    The filegroup "SECONDARY" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

     

    How to resolve this error?


    It looks like you have a filegroup named SECONDARY with no files.  You will need to add at least one file to the filegroup before you can use it.  Below is an example gleaned from examples in the Books Online (http://msdn.microsoft.com/en-us/library/bb522469.aspx):

     

    ALTER DATABASE MyDatabase
    ADD FILE
    (
        NAME= 'SECONDARY_Data',
        FILENAME = 'D:\DataFiles\MyDatabase_SECONDARY_Data.ndf'
    )
    TO FILEGROUP SECONDARY;
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Alexander Shakurin Friday, December 23, 2011 7:02 AM
    • Marked as answer by Maggie Luo Tuesday, December 27, 2011 8:48 AM
    Thursday, December 8, 2011 1:27 PM
    Answerer

All replies

  • Yes,if your table has a CI (means actual data) then everything moved to the filegroup and it should be working..Test backup restore possibilities having a file group.
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, December 7, 2011 2:35 PM
  • Please see my above reply

    • Edited by VijayKSQL Thursday, December 8, 2011 12:39 PM typo
    Wednesday, December 7, 2011 5:45 PM
  • Getting this error: - Unable to modify table. 
    The filegroup "SECONDARY" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

     

    How to resolve this error?

    Thursday, December 8, 2011 12:38 PM
  • Did you move a table into a secondary group?

    create database test

      on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),

    filegroup user_fg

      (name = 'datafile2', filename = 'c:\temp\datafile2')

    log on

      (name = 'logfile1', filename = 'c:\temp\logfile1')

    go

     

    use test

    go

     

    create table t1(col1 int)

    create table t2(col1 int) on [primary]

    create table t3(col1 int) on user_fg


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 8, 2011 1:18 PM
  • Getting this error: - Unable to modify table. 
    The filegroup "SECONDARY" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

     

    How to resolve this error?


    It looks like you have a filegroup named SECONDARY with no files.  You will need to add at least one file to the filegroup before you can use it.  Below is an example gleaned from examples in the Books Online (http://msdn.microsoft.com/en-us/library/bb522469.aspx):

     

    ALTER DATABASE MyDatabase
    ADD FILE
    (
        NAME= 'SECONDARY_Data',
        FILENAME = 'D:\DataFiles\MyDatabase_SECONDARY_Data.ndf'
    )
    TO FILEGROUP SECONDARY;
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Alexander Shakurin Friday, December 23, 2011 7:02 AM
    • Marked as answer by Maggie Luo Tuesday, December 27, 2011 8:48 AM
    Thursday, December 8, 2011 1:27 PM
    Answerer
  • Getting this error: - Unable to modify table. 
    The filegroup "SECONDARY" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

    getting above error while doing through management studio by table properties.

     

    Thursday, December 8, 2011 1:47 PM
  • Have you added a file to the SECONDARY filegroup?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, December 9, 2011 12:49 PM
    Answerer
  • Getting this error While adding file to secondary through Management Studio (Right click on Table -->design/modify-->properties)
    Friday, December 9, 2011 2:37 PM
  • You don't add files to filegroups using the table designer.  As an alternative to the T-SQL example I posted earlier, you can also right-click the database and select properties-->files and click the Add button to add a file to your SECONDARY filegroup.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, December 10, 2011 1:33 AM
    Answerer