Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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.

share|improve this answer
    
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 '12 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 '12 at 17:21

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.

share|improve this answer
    
LesterDove, did you try to defragment the free space as I told above? –  tcbrazil Jun 8 '12 at 13:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.