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

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

Re: A question about database file fragmentation

Show all headers | 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