locked
Database growth ratio on data drives after span the single database file in to multiple data drives RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    We have database for which the data file (mdf) size is 240 Gigs.

    We planned to shift the single mdf files in to 4 data drives by Drop & Re-creating the Clustered & NonClustered Indexes to an another filegroup that will be placed on other data drives. The data drives are E:, F:, G: & H:

    So, the plan to span data file is as follows:

    1. Moving the Clustered Index (with a capacity of 60 Gigs) of TableA to F: drive (by drop existing & recreate it on another filegroup placed in another drive.

    2. Likewise dropping & re-creating of 5 Nonclustered indexes of TableA (with the capacity of around 60 Gigs) to G: drive

    3. Dropping & re-creating another 20 Nonclustered indexes of 5 tables to H: drive.

    4. After completing step-1 to step-3, shrink the data file (mdf) to 60 Gigs and keep it on it's same drive, i.e. E: drive.

    Now, the data are span into all the 4 drives and the present status is as follows:

    Drive          Database file         Filegroup

    E: drive          MyDb.mdf             Primary_FG

    F: drive          Mydb1.ndf            Secondary_FG_TableA_Clustered

    G: drive          Mydb2.ndf            Third_FG_TableB_NC_Indexes

    H: drive         Mydb3.ndf             Fourth_FG_all_other_NC_indexes

    Now, the Question is:

    1. All the tables of this database will be populated with insert statements. Which drive will see the database growth ? only the default primary file group on E: drive or all the filegroups on other drives will also be loaded with their respective data ?

    1. If we insert data into TableA, the data will be written to which drive ? E: Primary_FG or F: Secondary_FG_TableA_Clustered ?

    2. Do we need to modify the regular insert statement to insert data into TableA since it has been re-created on another filegroup on another drive ?

    Thanks in advance.
    Wednesday, March 27, 2013 6:48 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    The drive that has the filegroup  on which the base table stays will have the data growth. Indexes are nothing but small subset tables stored internally and organized in B tree shape, they will also store data, so, when you are loading data, you will see data growth in the drive that has index filegroups for that base tables. So, both base table file group drive and non clustered index file group drive for that base table will have data growth.

    If you insert data to TableA , assuming your Table A stays on F drive(as per your example above), the data growth will seen in drive F. Table A has 5 non clustered indexes that stay on drive G, so this will also have data growth.

    You do not need to modify your insert statements, because you will be inserting into tables, which stay in the their respective filegroups.

    Hope it Helps!!



    Thursday, March 28, 2013 2:14 AM
    Answerer