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

Re: A question about database file fragmentation

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: A question about database file fragmentation
Date	Wed, 22 Jun 2011 22:09:08 +0200
Organization	Erland Sommarskog
Lines	47
Message-ID	<Xns9F0CE158B7ACCYazorman@127.0.0.1> (permalink)
References	<09281787-de10-4609-b01b-b3c738a3370f@y13g2000prb.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="700"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1++umZjlowiDmFx0y5y1ca8"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:ond/eSGDpPcELsPBjWNGfQSbCUo=
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:475

Show key headers only | View raw

joshsackett (joshsackett@gmail.com) writes:
> 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.
 
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@sommarskog.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

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