Issue
Generally, it is not necessary to defragment a SQL database. SQL generally does a good job of managing file resources. However, there are times when it may be necessary. One example is a database that is stored on a drive fairly full ( > 80% utilization). In this case, DB expansions are likely to result in excessive fragmentation, especially if you have not changed the default 1Mb DB expansion size.
Resolution
There are two major ways to defrag a SQL database.
- Take the database offline and detach it. Move the files to a second drive. Defragment the source volume and move the files back, then re-attach the database.
- The second option lets you defrag the files where they sit, while users are using the database. Download the Contig.exe utility below. This is a defragmentation utility produced by SysInternals that allows you to defrag a single file. Run contig /? from the command line to retrieve a list of available commands. Notably, contig -a <filename> will return an analysis of the file and list the number of fragments. Contig also supports wildcards, so the command contig -a -s *.mdf will return a list fragmentation in all .MDF files in the current folder and all sub-folders.
Lastly - Please, for gosh sakes, backup any files before you make such a low-level change.
Resources
http://technet.microsoft.com/en-us/sysinternals/bb897428
Syntax
Usage:
contig [-a] [-s] [-q] [-v] [existing file]
or contig [-f] [-q] [-v] [drive:]
or contig [-v] -n [new file] [new file length]
-a: Analyze fragmentation
-f: Analyze free space fragmentation
-q: Quiet mode
-s: Recurse subdirectories
-v: Verbose
Contig can also analyze and defragment the following NTFS metadata files:
$Mft
$LogFile
$Volume
$AttrDef
$Bitmap
$Boot
$BadClus
$Secure
$UpCase
$Extend
