Defrag an MDF file in a VM

[PGO01]

Not sure if this is the correct forum.

I have a 20Gb SQL file (MDF) that is completely fragmented - causing an overall fragmentation on my VM of 45%.

I've rebuilt all of the indexes through the DBCC command in T-SQL and stopped all SQL services, rebooted and done a windows defrag (from within the VM).

The MDF file doesn't get defragged. I've tried detaching the 20Gb database and defragging - but still no improvement.

I've tried Windows defrag, Diskeeper, and UltimateDefrag.

Anyone able to help? I really need to optimise this VM and preferably shrink it afterwards so I definitely can't have 45% fragmentation.

Many thanks in advance!

 

[mrdenny]

First rebuilding the indexes won't change the physical file fragmentation.

The only way that you'll be able to defrag the file will be with the SQL Server stopped. Do you have enough free space on the drive to defrag the file?

Worse comes to worse, move the file to another VM, or another disk on that VM, then defrag the drive. Then copy the file back. The file will now be in one large contiguous block (assuming that there's enough space in one large contiguous block). Keep in mind that what Windows sees as a fragmented file means nothing. The guest disk is just a physical file on the physical RAID array, and the data is spread across that RAID array. Just because Windows on the guest see's the file at contiguous doesn't mean that it actually is.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog