Groups > comp.databases.ms-sqlserver > #473

A question about database file fragmentation

From	joshsackett <joshsackett@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	A question about database file fragmentation
Date	2011-06-21 16:31 -0700
Organization	http://groups.google.com
Message-ID	<09281787-de10-4609-b01b-b3c738a3370f@y13g2000prb.googlegroups.com> (permalink)

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

A question about database file fragmentation joshsackett <joshsackett@gmail.com> - 2011-06-21 16:31 -0700
  Re: A question about database file fragmentation Erland Sommarskog <esquel@sommarskog.se> - 2011-06-22 22:09 +0200
  Re: A question about database file fragmentation "Fred." <ghrno-google@yahoo.com> - 2011-06-24 12:07 -0700

csiph-web