Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL versions from 2005-2011 and Windows versions from 2003 and 2008 R2. If it matters, the product I'm talking about is PerfectDisk, but I believe there are similar programs that work the same way.

Testing so far hasn't turned up many problems aside from occasional I/O performance issues, which is no surprise and can be worked around by rescheduling & tuning the defragmentation. However, I'm more concerned about the risk of data corruption.

Does anyone here have experience with running this type of software in production on database servers? Did you experience any data corruption?

I'm rather uneasy about this despite being unable to find any solid evidence that it poses a problem.

Thanks for any replies.

share|improve this question
1  
I'd be asking how SQL server disks got so fragmented; proper planning and space management makes fragmentation a non-issue. – SqlACID May 31 '12 at 11:28
Yes, that is definitely a valid point. Unfortunately getting thousands of clients and even more servers (that we aren't even the primary sysadmins of) to plan and manage space reasonably is probably not realistic at this point. – James May 31 '12 at 14:25

1 Answer

up vote 6 down vote accepted

Personally, I wouldn't want any antivirus or defrag software anywhere near a database file. Ask yourself, is this going to cause more problems than it's going to solve?

From A tale of CHECKDB failures cause by 3rd party file-system drivers:

At the end of last week, Diskeeper 2009 was upgraded to Diskeeper 2010 on the servers by our server team. Diskeeper 2009 had been there for at least a year without problems so who would have expected it to be the issue. Turns out the new version has an option enabled called IntelliWrite that causes this problem to occur. Disabling this option immediately resolve the problem. Paul blogged about this problem a long time ago, and recalled reading this when I clicked on it but it was the last thing that I would have thought about.

From Beware 3rd party file-system drivers with DBCC CHECKDB

I don't want to cry 'wolf' here, but be careful of attributing massive corruption problems to the real IO subsystem if there's any kind of file-system filter driver installed.

With proper planning and management, you shouldn't need to defrag your database files.

share|improve this answer
3  
+1 file level defrag for SQL is definitely barking up the wrong tree... And messing with files under SQL is like wearing a red target and a 'Corrupt me here' callout... – Remus Rusanu May 31 '12 at 11:52
Thanks Mark and Remus. This is exactly the sort of thing I'm looking for. I'll see what else I can come up with. – James May 31 '12 at 14:27
MS has also published a warning here: support.microsoft.com/kb/2454053 – James May 31 '12 at 14:48

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.