Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: A question about database file fragmentation

Show all headers | View raw

On Jun 21, 7:31 pm, joshsackett <joshsack...@gmail.com> wrote:
> 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.

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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