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.