This issues was brought to me by our Sr. Network Engineer. While running operating system level de-fragmentation using either windows de-fragmentation or third party tool it always skip all the MDF file and never de-fragment them. He was wondering why this happens all the time.
The reason MDF file are skipped all the time in de-fragmentation because they are in use when SQL Server is running. Windows operating system de-fragmentation skips all the file in are currently in use.
After discovering this the real question was how to de-fragment when files are in use. Steps are Stop the Server, Re-start, keep the SQL Server services off and do de-fragmentation.
What if Server can not be taken off-line? In that case, nothing can be done. In my company SQL Server runs on redundant configuration – they are mirrored real time as well fail-over clustering is configured. We took one server down and system ran on safely on fail over server while we ran de-fragmentation on other server. We repeated the same order for all server. We see significant difference in our operating system performance as well as database response time.
One more thing to note, operating system needs more than 15% empty space to do optimal de-fragmentation.
Just for fun I will type my conversation with our Sr. Network Engineer. He is very smart person.
Sr. Network Engineer : Hey Pinal, MDF files does not de-fragment every night.
Pinal : May be because they are in use. Needs to alternate and de-fragment on cluster, what do you think?
Sr. Network Engineer : Yeah! Thanks. It will be good test for fail-over as well. Will do tonight.
Reference : Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
with your entry, I understand that ”
every month, I ‘ll stop MS SQL Server for defragmenting disk drives ”
regards
ozgur
I am not sure I understood this well. At our fail over cluster mdf files are at clustered resources that is disk on storage and when I fail over to other node all resources fail over too so only c drive is left for defrag and I can do that anyway
Hi Pinal,
I too don’t know if I understood this correctly(as irena mentioned). In Clustering, when we failover we have the same MDF files moving to other node leaving them to be still in use. How fragmentation can be done in such scenario unless I use mirroring or logshipping where they have different MDF and LDF files.
Thanks.