Defragmenting a partition with a large heavily fragmented database file

I'm sure you've come across this before, you have a 100GB partition with a 60GB database mdf sprawled across the partition in 8600 fragments. Nice! IO performance will be severely impacted, especially if this partition (more often than not) is on a RAID 5 array. In addition to this there is only 15% free space on this partition. Now your task is to defragment the partition and the mdf.

Well the easiest way to accomplish this, if you have another partition with enough space to temporarily house the 60GB mdf is to move the mdf, defrag the data partition and move the mdf back, hopefully into one contiguous space. This is a classic DBA scenario which involves some T-SQL or SSMS work, some OS file system work, some OS defrag work, more file system work and then finally some T-SQL work. All good and well, but now you want to automate it, so that you don't forget about your quest halfway through the process.

Enter Powershell. Sure, you could do some of the file system stuff using xp_cmdshell. But that little gem should be locked down no? And what about the defrag? Re-enter Powershell.

In a few lines of code this process can be accomplished from start to finish. In the code below I've created a function that does the tasks outlined above. That is:

1. Detach the database


2. Move the mdf and ldf files


3. Defragment the fragmented partition


4. Move the mdf and ldf files back


5. Attach the database

Code:

Function DetDefAt {
Param ([string]$svr, [string]$dbname, [string]$mdf, [string]$ldf, [string]$mdft, [string]$ldft, [string]$Drive2Def)

#detach db
$s = get-sqlserver $svr
#get-sqldatabase $svr $dbname
$s.KillAllProcesses($dbname)
$s.DetachDatabase($dbname,"true")

#move files
move-item $mdf $mdft;
move-item $ldf $ldft;

#defrag
$v=gwmi win32_volume
$v1=$v | where {$_.name -eq $Drive2Def}
#$v1.defraganalysis().defraganalysis
$v1.defrag($true)


#move files back
move-item $mdft $mdf
move-item $ldft $ldf 

#attach db
$f = New-Object -Type System.Collections.Specialized.StringCollection
$f.Add($mdf)
$f.Add($ldf)
$s.AttachDatabase($dbname, $f)


}

Cool. The function takes 6 input parameters, the server name that the above task will be run against, the database, the mdf and ldf, the temp locations where the mdf and ldf will be moved to and the partition to defrag.