Hi,

I'm running Windows 2003 R2 sp2 x64 Standard with SQL Server 2005 installed.
I want to defrag the data volume but I'm getting the following error
message:

Defragmentation is complete for : Data(D
Some files on this volume could not be defragmented.
Please check the defragmentation report for the list of these files.

I view the report and see that it lists few of my main database files but it
doesn't tell me why they are not being defraged.

The largest file that isn't defraging is 34GB, the volume has 52% free
space, 132GB free but has 96% file fragmentation. What do I have to do to
get these files to defrag? Or should I not be defraging this volume?

Thanks in advance,
Linn

Linn Kubler (lkubler (AT) chartwellwisc2 (DOT) com) writes:
Are you using the defragmentor which is in Windows? That one does not
report which files that are defragmented, does it?

Normally, you should not have to defragment SQL Server database files, of
the simple reason that you should plan ahead and make sure in advance they
have a size that fits the anticipated data volume. But if you decline to
that, and rely on autogrow, then fragmentation can certainly become an
issues that reduces the performance.

I would suggest that you invest in a third-party defragmenter which is more
able than the built-in one. Personally, I use Diskkeeper from Executive
Software, and Diskkeeper gives me a report of fragmented files. I'm
uncertain whether it can defragment files that are constantly open like
database files. (And I am not sure that I would like it to try.) But as I
said, that should not really happen. If it happens, you may have to
take that database offline.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx