Posted by: Denny Cherry
Everyone likes nice defragmented SQL Servers. However if you use Diskeeper to do so be very careful before you upgrade to the new version 10, or if you are going to upgrade to the new version 10 you’ll need to make a small change to your configuration on each SQL Server.
You see the problem that Diskeeper has a new feature called IntelliWrite. This feature is a filter driver which is where the problem comes from. A filter driver intercepts the write operations and does something else with them. By it self this isn’t a dangerous thing to do, however if not done correctly it can have disastrous results. In this case the filter drive is attempting to ensure that all files on the hard drive are contiguous, which would be a good thing.
In this case the filter driver works correct during normal operation of the SQL Server. However the problem comes into play when you run a DBCC CHECKDB command. When this happens the engine will report that it can’t access the data file correctly.
This is because of the way that SQL Server handles DBCC CHECKDB. It does this by using an alternate data stream to the data file. However the filter driver that Diskeeper has written doesn’t handle alternate data streams correctly.
Now this isn’t the first filter driver to cause major problems like this, and it won’t be the last. Fortunately the problems caused by Diskeeper aren’t that major, and you can simply turn off this feature and the problem goes away. However if you don’t run DBCC CHECKDB regularly on your databases, and you had this driver installed and didn’t know that it was in there (apparently it is enabled by default) and you ended up some database corruption when you tried to fix the problem using DBCC CHECKDB and you would end up with even bigger problems.
Now as the DBA hopefully before you have installed Diskeeper on your production databases you installed them on your QA database servers and had every job run just like it would against your production database. If you haven’t done this, and you don’t do this every time you open yourself up to risk with every application that you install. No matter if you think that it impacts your database or not, it should be tested against your QA system ahead of time. Unfortunately this takes time, sometimes a lot of time. But the end result against your production environment will be a much better result.