HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

SQL Server database file fragmentation

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
filefragmentationsqldatabaseserver

Problem

I have inherited a system where the previous DBA added 7 data files to the PRIMARY filegroup (8MB initial size) and left the AUTOGROW option at 8MB. What I have now is a set of eight files each about 3 - 4GB in size that have been slowly growing over a two-year period. I'd like to remove the file fragmentation in the fastest way possible.

Here are the options that I came up with:

  • Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB)



  • Move the data off each of the successive files and mark them for deletion



  • Delete the other 7 files



  • Detach the database



  • Copy the detached database file to a different drive on the server



  • Copy the detached database file back to the original drive



  • Reattach the database



or

  • Create a new database 32GB in size (8 x 4GB)



  • Transfer all of the objects, tables, users and permissions to the new database using SSIS



  • Drop the old database



  • Rename the new database



Is an OS level "Disk Defrag" still not an option?

I do not know which option is the best or if it will even work.

Also, this database is being mirrored and replicated, so the least amount of work in regard to having to rebuild that it optimal.

Thanks for your help.

Solution

There are some useful answers to the following question on Server Fault, which may help
https://serverfault.com/questions/31011/sql-database-physical-file-fragmentation

Context

StackExchange Database Administrators Q#3446, answer score: 3

Revisions (0)

No revisions yet.