locked
Change Default File group RRS feed

  • Question

  • Hi All,

    I have 500GB database with 7 Data files.

    One data file is on X: Drive - Primary file group - Default- Has only one file.

    Other five on Y: Drive. - Secondary file group SEC_1

    I have added a new data file on Z: Drive, create a new secondary file group SEC_2, as Y: Drive was filling up quickly and also to leverage number of writes.

    Is it preferable to change the filegroup SEC_2 to default?

    Any help appreciated

    Thanks.

    KRanp.

    Monday, December 29, 2014 10:44 PM

Answers

  • I have added a new data file on Z: Drive, create a new secondary file group SEC_2, as Y: Drive was filling up quickly and also to leverage number of writes.

    Is it preferable to change the filegroup SEC_2 to default?

    If it is newly created tables without a filegroup specification that are the culprit, then marking SEC_2 as the default filegroup may help without other changes.  But if it is existing tables that are the problem, you'll need to move those to the new filegroup (e.g. recreate the clustered indexes with DROP_EXISTING).  You could also move existing secondary files to the Z drive.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP, Editor Tuesday, December 30, 2014 7:36 AM
    • Marked as answer by kranp Tuesday, December 30, 2014 3:08 PM
    Tuesday, December 30, 2014 1:44 AM
    Answerer
  • Default FG setting would be used while creating new table. For existing you need to move data (or use DROP_EXISTING with ALTER INDEX)

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by Olaf HelperMVP, Editor Tuesday, December 30, 2014 7:36 AM
    • Marked as answer by kranp Tuesday, December 30, 2014 3:08 PM
    Tuesday, December 30, 2014 1:46 AM

All replies

  • I have added a new data file on Z: Drive, create a new secondary file group SEC_2, as Y: Drive was filling up quickly and also to leverage number of writes.

    Is it preferable to change the filegroup SEC_2 to default?

    If it is newly created tables without a filegroup specification that are the culprit, then marking SEC_2 as the default filegroup may help without other changes.  But if it is existing tables that are the problem, you'll need to move those to the new filegroup (e.g. recreate the clustered indexes with DROP_EXISTING).  You could also move existing secondary files to the Z drive.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Olaf HelperMVP, Editor Tuesday, December 30, 2014 7:36 AM
    • Marked as answer by kranp Tuesday, December 30, 2014 3:08 PM
    Tuesday, December 30, 2014 1:44 AM
    Answerer
  • Default FG setting would be used while creating new table. For existing you need to move data (or use DROP_EXISTING with ALTER INDEX)

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Proposed as answer by Olaf HelperMVP, Editor Tuesday, December 30, 2014 7:36 AM
    • Marked as answer by kranp Tuesday, December 30, 2014 3:08 PM
    Tuesday, December 30, 2014 1:46 AM
  • Hello ,

    USE master;
    GO
    ALTER DATABASE AdventureWorks2012 
    MODIFY FILEGROUP Test1FG1 DEFAULT;
    GO
    ALTER DATABASE AdventureWorks2012 
    MODIFY FILEGROUP [PRIMARY] DEFAULT;
    GO

    Details documentation is Making a filegroup the default


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, January 3, 2015 8:32 PM