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 (http://blog.SQLAuthority.com)
with your entry, I understand that ”
every month, I ‘ll stop MS SQL Server for defragmenting disk drives ”
regards
ozgur
[...] SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance [...]
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