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 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: 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 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