Post reply

Adding a new file to the database primary file group

  • April 3, 2008 at 10:27 am

    #69762

    Hi, I have the code to add a file to the primary file group of a database and it worked. How do I tell SQL 2000 to use the new file? How does SQL use two primary group files? Does one fill up first, then the new one is used? How do I control what file is used?

    Code I used to test it is below:

    ALTER DATABASE Testdb2

    ADD FILE (NAME = Testdb2_DataNEW,

    FILENAME = 'D:\MSSQL\data\MSSQL\Data\Testdb2_DataNew.ndf',

    SIZE = 5MB)

    thanks,

  • April 3, 2008 at 3:15 pm

    #798109

    Dana, if you have more than one file in a filegroup you cannot control which SQL writes to,

    SQL writes to multiple files in a round-robin fashion on a percentage fill basis. i.e. it tries to keep all files equally filled by writing to emptier files more, or full files less, whichever way you prefer to look at it. So in theory when one file grows, the other will grow soon afterwards.

    To my mind this is a good reason to keep files sized the same to evenly spread the load.

    BOL has good info on this.

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

  • April 4, 2008 at 5:56 am

    #798338

    Thank you for the response.

  • April 4, 2008 at 12:26 pm

    #798619

    Can a Database expansion of let's say 1GB be done while users are accessing the database?

    Also, Can the add database file to the primary filegroup be done while users are using the database?

  • April 4, 2008 at 12:51 pm

    #798637

    yes to both questions, try and do it at a quieter time ,though adding 1 GB should be pretty quick (depending on hardware)

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

  • April 4, 2008 at 1:02 pm

    #798646

    Great, thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply