Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.
We scheduled a task to defragment this drive at 3:00 a.m. on a Saturday morning. The job completed after 40 minutes with no apparent errors. However, the drive remains heavily fragmented.
Should we have stopped SQL Server service(s) before defragmenting?
CONTEXT
Per requests for context: We have a Microsoft SQL Server 2005 instance (9.00.5324.00) running 32-bit Windows Server 2003 (SP2) on Dell PowerEdge 2950 hardware, circa 2007, with 4GB RAM. The PowerEdge 2950 has four 68GB drives configured as RAID-1 to create two 68GB virtual disks: (1) C (boot and OS) & D (pagefile, miscellaneous other data); and (2) E (SQL data). To my knowledge, IT staff have never defragmented any of these drives…Disk Defragmenter reports file fragmentation of 66% (C), 77% (D), and 99% (E). Performance Monitor reports the following average results: "Paging file: % usage" = ~6.8%; "SQL Server: Buffer Manager – Page life expectancy" = 20 seconds; and "PhysicalDisk: Avg. disk sec/write, drive E" = between 300 and 1,100 ms. We're due for a much-needed hardware and SQL Server upgrade in a few months time (viz., new hardware, 64-bit Windows Server 2012, 64-bit SQL Server 2012, 12GB RAM), but, due to end-user performance, want to alleviate the issue as much as possible. Thus the thinking a file defrag might help for drive E, the main SQL data drive.
As an aside, last week we pulled two failed drives and rebuilt the array…not sure that matters. We contract with another IT team to maintain the server, so we do not have direct access to the equipment…our organization just pays for services.
We can afford the downtime during regularly scheduled maintenance windows (weekly) as well as out-of-band downtime, as necessary, overnight.
Best Answer
I personally have used Raxco PerfectDisk (not associated with the company or any of their employees in any way) to do online defrags of SQL Server LUNs. Works perfectly, if it does slow the server down a bit. I would recommend doing it during periods of lighter activity. When I say "works perfectly" I am referring to it not corrupting the volume or the SQL data files.
The built-in defragmenter does a very poor job if certain structures are fragmented on the drive. PerfectDisk shows you details about all the items that are fragmented including the NTFS allocation tables, directories, alternate file streams, etc. etc.
Does PerfectDisk defragment SQL databases? http://support.raxco.com/KB/a106/does-perfectdisk-defragment-sql-databases.aspx
See this archive copy of the Technet News Magazine regarding SQL Server and fragmentation: http://web.archive.org/web/20100803204458/http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx