Do MDF Files Get Defragged?

When you run a disk defragmenter, such as the built-in Windows one, or a third-party product, do your MDF and LDF files get defragmented if SQL Server is running? I get asked this question fairly often by "reluctant DBAs".

(Be mindful that we're talking about disk-level fragmentation here, not database-level, such as "logical" or "external" index fragmentation - that's another thing altogether.)

First, defragmentation of the mdf and ldf files should – in a ideal world - rarely be an issue. If you remembered to defragment the disk first, and this gave you a large enough empty space to hold the entire ?df file, and if you created the database files big enough to handle all the data you'll ever need to store, and you made sure that "AutoShrink" is turned off... then you wouldn’t need to worry about disk fragmentation. That's a lot of if's, and anyway, the database may grow bigger than you predicted, and be forced to auto-expand; that’s when disk fragmentation begins. So, we can't really avoid the issue.

Second, will the disk defragmenter work on "open" files, like our ldf and mdf files? Yes. A disk defragger works at a lower level than the file system: it knows nothing of file locks, streams, "open" files, etc., so the fact that the MDF file is open is immaterial.

Third, is it safe? Well, Microsoft hardy ever uses words like "perfectly safe" and "CANNOT" - but in this case, they do:

• "Given that file defragmentation is integrated in the operating system, defragmenting open files is perfectly safe." ( http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx )
• Microsoft's documentation for NtFsControlFile( ) states, "[NtFsControlFile( ) internals] are implemented such that you CANNOT corrupt data on your drive by using them."

Of course, defragmenting can slow any disk-intensive application, including SQL Server. Microsoft says, "I/O activity generated must be considered if continuous high performance is mandatory." ( http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx ). Some defraggers may detect disk I/O activity and "get out of the way". Generally, though, you’d want to defrag the disk during maintenance hours or low usage times.

So, defragment your hard drive (to create a big empty space), and then expand your database (hopefully forcing the OS to move the mdf and ldf files to that big, empty space). Make sure "AutoShrink" is off. And remember that a big empty database will normally be faster than a small full one.