Hi,
I have inherited a system where the previous DBA added 7 data files to
the PRIMARY filegroup and left the AUTOGROW option at 8MB. What I have
now is a set of eight files each about 3 - 4 GB in size that has been
slowly growing over a two-year period. I'd like to remove the
fragmentation in the fastest way possible.

Here are the options I can think of:
1. Expand the 1st file in the PRIMARY filegroup by ~28 GB (7 files x 4
GB)
2. Move the data off each of the successive files and mark them for
deletion
3. Delete the other 7 files
4. Detach the database
5. Copy the detached database file to a different drive on the server
6. Copy the detached database file back to the original drive
7. Reattach the database

or

1. Create a new database 32 GB in size (8 x 4 GB)
2. Transfer all of the objects, tables, users and permissions to the
new database using SSIS
3. Drop the old database
4. Rename the new database

I honestly do not know which is the best or if it will even work.
Also, this database is being mirrored and replicated.

Thanks for your help.

joshsackett (joshsackett (AT) gmail (DOT) com) writes:
If I understand this, the problem is that you have fragmentation within
the file system. In this case the easiest is simply to run you favourite
file-defragmentation software on the drives. I guess you need to set
the databases offline.

Of course, you may find that there is no much point with all these
seven data files, and I can agree that it is an overkill for a database
of that size. Then again, there may not be harm enough with to warrant
a complex reorganisation.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Jun 21, 7:31*pm, joshsackett <joshsack... (AT) gmail (DOT) com> wrote:
I with Erland on this. If you have the space on your volume, taking
the datbases off line and doing a file system defrag should provide
considerable help. I once had a 7 hour job drop below 2 hours as the
result of this sort of cleanup.

I've been heeding the fragmentation reports' tendency to exclude
fragments over 64 MB. With my databse files on dedicated volumes,
I've given all the files sharing a volume the same 128 MB or larger
autogrow parameter, in the hope that this will eliminate
fragmentation. I don't have any hard data but I've been running for a
couple of years without defragging these volumes or noticing
degradation.

As long as your allocations are of uniform size like this, I wouldn't
expect a combined database to perform better. And, having the data
glommed together in one database could make future reorganization
difficult.

Fred.