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?

  • 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.
    – Hannah Vernon
    Dec 13, 2012 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.
    – Hannah Vernon
    Dec 13, 2012 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. Dec 13, 2012 at 4:48

1 Answer 1


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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