I have inherited a system where the previous DBA added 7 data files to the PRIMARY filegroup (8MB initial size) and left the AUTOGROW option at 8MB. What I have now is a set of eight files each about 3 - 4GB in size that have been slowly growing over a two-year period. I'd like to remove the file fragmentation in the fastest way possible.
Here are the options that I came up with:
- Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB)
- Move the data off each of the successive files and mark them for deletion
- Delete the other 7 files
- Detach the database
- Copy the detached database file to a different drive on the server
- Copy the detached database file back to the original drive
- Reattach the database
or
- Create a new database 32GB in size (8 x 4GB)
- Transfer all of the objects, tables, users and permissions to the new database using SSIS
- Drop the old database
- Rename the new database
Is an OS level "Disk Defrag" still not an option?
I do not know which option is the best or if it will even work.
Also, this database is being mirrored and replicated, so the least amount of work in regard to having to rebuild that it optimal.
Thanks for your help.