• I only considered doing this once but never had to carry it out (we ended up migrating to new servers) so was curious towards the responses. I was watching this thread (and the one in the 2012 forum) to see if anyone would jump in, but for lack of an expert wanting to weigh in I'll throw in my lowly two cents.

    When I was considering it my plan was to:

    1. run CHECKDB

    2. take backups

    3. stop the service

    4. test the restores somewhere

    5. move all the database files to another drive available on the same server (no network involved)

    6. reformat the drive (you could also defrag the drive here if reformatting would cause other pain)

    7. bring all database files back from temporary drive location

    8. start the service

    9. run CHECKDB

    This was only an option because I had the maintenance window and the drive space to do something like this and there were many databases on the instance that were growing at 1MB per grow operation for several years so you can imagine the physical fragmentation that was in play. Moving files is risky though. I would have had good backups but it could have made for a huge setback if one of my files became corrupt during the process.

    You only have one file you're interested in defragging so if I had to choose to start somewhere I would maybe try the way I outlined if you have the space and time or try option 3 with the Sysinternals defragger. I haven;t used it but that group puts out some superior tools so I would have no qualms about trying it. I would definitely run CHECKDB before (as my control group) and after (as my compare group) though just to make sure the process was not destructive to any bytes in the file. Of course it should go without saying that taking good backups (and testing they are valid) is highly important before attempting anything like this.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato