0

I am defragmenting a badly (95%) fragmented drive upon which large (35 gig) SQL Server 2008 .mdf files live. After defragmenting and viewing the exception report, I see that the production .mdf file that I'm most interested in could not be defragmented. I initially figured it was because MSSQL had an exclusive lock on the file, so I detached it and tried again. No luck - this particular .mdf file could not be defragmented. What am I missing? Most online references suggest that I should be able to file defrag an .mdf

A note: yes, I'm talking about file defragmentation, not index defrag, which is already being done routinely, and which I'll re-run after this.

Thanks! What am I missing?

Improve this question

2 Answers 2

Reset to default
2

What am I missing?

Nothing, it should normally defagment it. Not that deframgmenting it makes sense given that - well - th e content are fully random access anyway. More important to have a NTFS file system with 64k nodes (standard is 4) being properly aligned (in cae it is a RAID drive) to avod split IO.

But it should defragement it.

Improve this answer
2
  • Thanks, that's good info. But my question is not whether I should defragment it, but rather why it's not letting me. After defragging the drive, the MDF comes up in the list of files that could not be defragmented.
    – LesterDove
    May 25, 2012 at 13:02
  • Acutally the info is my level of junior SQL Server DBA interfiew question - do not know how to set up a good hard disc setup and you are not even junior. NO idea, AT LEAST When SQL Server is turned off there is no reason it should allow defragmentation. THose files are normal files.
    – TomTom
    May 25, 2012 at 17:21
1

LesterDove, the problem may be your .mdf file size: your disk have to have space to re-allocate a 35-gig file, in order to defragment it.

You don't mention what tool you're using to defrag the disk, I supose it's the standard Disk Defragmenter. Try to use another defrag tool that let you defrag the free space (I use Defraggler from Piriform), in order to create sufficient space to defrag your .mdf to a contiguous file.

Improve this answer
1
  • LesterDove, did you try to defragment the free space as I told above?
    – tcbrazil
    Jun 8, 2012 at 13:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .