Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime.

I have a plan that I am not sure will work:

  1. Normally defrag the partition while the service is still running until the only fragmented files are the database files.
  2. Enter single user mode.
  3. Make complete backup to a different partition.
  4. Delete the database with fragmented MDF file.
  5. Create/restore new database from the recently created backup.

Will dropping database and then restore from backup remove the file fragmentation?

Or should I just turn off the service and run defrag tool until it finishes?

share|improve this question
    
If you are using a modern defrag utility like Raxco PerfectDisk you don't need to turn off the instance. However, if you don't NEED the instance running then turn it off first. –  Max Vernon Dec 13 '12 at 4:31
    
And keep in mind, this won't remove fragmentation that is internal to the database files. You should look up Index Fragmentation and the ALTER INDEX REBUILD command. –  Max Vernon Dec 13 '12 at 4:32
    
Windows-level file fragmentation and index fragmentation within SQL Server are completely different things. Also taking a backup and restoring it will restore exactly what you backed up - there will be no magic defragging no matter what volume you restore to. What you might look into is creating a new file group, with a properly sized file, and create all of your indexes there with DROP_EXISTING. –  Aaron Bertrand Dec 13 '12 at 4:48
add comment

1 Answer

up vote 2 down vote accepted

There is a great discussion of this topic here at ServerFault in which the 3 forms of fragmentation are discussed. I always have to remind myself to search for the answer before I post a question as it saves me so much time.

Short answer (tl/dr):

It will remove physical fragmentation but not logical or VLF fragmentation within the log file.

Physical Fragmentation:

Backing up and restoring will remove any physical fragmentation. That is, fragmentation that occurred on the NTFS partition as a result of your database growing incrementally over time and contiguous space not being available as a result.

Other Fragmentation:

You will still have index fragmentation and VLF Fragmentation within the database. Paul Randall stressed not using Windows Defrag at his SQLSkills Immersion Events, and how it could lead to database corruption so I think you're on the right track.

Background:

Physical fragmentation will operate on the disk where the MDF/NDF/LDF files occupy. Since SQL Server does not track this information as it would be useless. We want to be able to restore databases to any hard drive as long as it has space, we're not concerned with where it gets located on the disk so much unless you're short writing...but that's a exception to the rule.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.