August 4, 2005 at 1:36 pm
Hi there,
We have SQL2K database grown too big that I have to add additional disk to the system. Question is, how do I split the existing DB.mdf (500GB) into 2 250GB DB.mdf and DB.ndf and place them into separate disks? what are the choices and the fastest way?
Thanks.
August 5, 2005 at 2:22 am
Re creating the Clustered indexes (Tables) and Non clustered indexes is the best way to distribute your existing data. you may also move all non clustered indexes to the new table by re creating them. Use DBCC DBReindex to do this.
Using other methods like exporting the half data, dropping the tables and then importing again would require additional space and more time.
Hope this helps
M.S. Reddy
August 5, 2005 at 2:37 am
I would probably do something like this:
1. Create a new set of files in a new filegroup. I would not create 2x250GB files, since that is just asking for the same problem to occur again. I would create a set of smaller files that can be easily moved to different disks in the future if needed. Do not let these files autogrow, instead add new files as necessary.
2. Possibly create another filegroup and set of files for nonclustered indexes.
3. Drop nonclustered indexes on table(s).
4. Drop clustered index on table(s).
5. Create clustered index on table(s), specifying that they should be created on the new filegroup.
6. Create nonclustered indexes on table(s), specifying that they should be created on the new filegroup (possibly their own new filegroup).
7. Shrink the 500GB file in the primary filegroup to a suitable size for the remaining system objects that will be stored there.
8. Configure the database to add new tables by default to the new filegroup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply