SQL Server Performance

Best way to add data files to existing database?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DBADave, Jan 27, 2009.

  1. DBADave New Member

    What is the best way to expand a database from one data file to multiple data files? I want to create multiple data files each on their own RAID 10 array. I was told I had to create a blank database with the desired number of data files and then script the objects from the old database onto the new database and then BCP the data or use SSIS to copy the data. Is there another method?
    Dave
  2. rohit2900 Member

    You can use alter database to add the new data file and then move existing tables to this file. As initally the file group is primary you need to change it to newly added file.
  3. MohammedU New Member

    Is there any reason to add the additional files?
    I don't see any reason to expand the same file group. It is better to create the new file group and add mulitple files to it instread of adding to primary filegroup. If you are using SQL 2005 it will reduce your down time of db restore incase you need to restore incase of disaster.
    I will do the following...
    1. Backup and Restore the backup of existing db
    2. Create new filegroup and make it as default filegroup and add multiple files to it as needed.
    3. use ALTER index to move the table to different filegroup.
  4. DBADave New Member

    We have a significant disk bottleneck with our data drive. Currently 19 databases all share the same data drive. We want to move several databases to a new drive. Once option is to create a new RAID 10 array and move 5 databases (data files only) to the new array. Another option is to create 3 arrays of RAID 10 and sread the data files across all arrays. For example:
    ALTER DATABASE MY_Database
    ADD FILE (NAME = MY_Database_DATA2, FILENAME = 'H:Microsoft SQL ServerMSSQL.1MSSQLDataMYDatabase_DATA2.NDF')
    ALTER DATABASE MY_Database
    ADD FILE (NAME = MY_Database_DATA3, FILENAME = 'I:Microsoft SQL ServerMSSQL.1MSSQLDataMYDatabase_DATA3.NDF')
    ALTER DATABASE MY_Database
    ADD FILE (NAME = MY_Database_DATA4, FILENAME = 'J:Microsoft SQL ServerMSSQL.1MSSQLDataMYDatabase_DATA4.NDF'')
    The problem is how do I evenly distribute the existing data to all data files?
  5. Elisabeth Redei New Member

    Hi Dave,
    You will have to recreate your indexes onto the new filegroup (that has several files) with something like this:CREATE CLUSTERED INDEX
    non_cl_guid ON test_guid(col1)WITH DROP_EXISTING
    ON myNewFileGroup
    HTH
    /Elisabeth

Share This Page