Avatar of Jerry Seinfield
Jerry Seinfield
 asked on

A heavily fragmented file in an NTFS volume may not grow beyond a certain size. Operating system error 665. EvenID 17053 MSSQLServer

Hello  server and SQL EXPERTS,

I need the expertise from both teams, in an issue found in one of my application servers that runs a third party application called Informatica. This application runs some jobs that uses SQL resources, apparently the jobs are performing very slow.

Our DBA guys ran a checkDB and found the following[ see attached screenshot]

and below is what’s recorded in the event log, please take a look while I’m running DBCC checkdb. The error is on a data file that has autogrowth off, and it’s on a different mount point, i.e., data3:

 After the DBA completed DBCC CHECKDB, he started noticing some improvements on the load and blocking, but as this seems to be a known issue with “967351  A heavily fragmented file in an NTFS volume may not grow beyond a certain size” ,which requires a window’s fix:

https://support.microsoft.com/en-us/kb/2002606


 Can you please confirm this is the right solution to fix this isue? and why, please elaborate an action plan step by step, and please provide the hotfix if required

Please elaborate your answer with your thoughts about the issue found and the "possible resolution" any gotchas?
CHECKDBError.jpg
Microsoft SQL ServerWindows Server 2008Windows Server 2012

Avatar of undefined
Last Comment
David Johnson, CD

8/22/2022 - Mon
Jerry Seinfield

ASKER
I am very confused here, the hotfix applies to WIndows 2008 R2, but we found the error on a Windows 2012 R2

please, provide instructions step by step to resolve this issue
ASKER CERTIFIED SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

If you have enough free space on the volume to hold a defragmented version of the file, you can use contig.exe to defragment the file itself, or whatever part of it can be defragmented.  I've used this on *live* SQL files many, many times and never had an issue.

You should also increase the automatic growth amount for the db to keep from getting too many small fragments on disk, particularly if you have IFI set on (which you should).  Also, be sure to specify growth as a fixed amount and not a percentage.
Jerry Seinfield

ASKER
thanks Scott,

Will defrag.exe –c –h –k –g caused serious performance issue?  what is the impact of defrag a drive that has SQL data?

in the meantime, can you please provide more details about the use of contig.exe and the"LIVE" SQL files
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Scott Pletcher

You can Google "contig.exe".  I don't know about defrag.exe.
David Johnson, CD

Sysinternals Contig.exe https://technet.microsoft.com/en-us/sysinternals/bb897428.aspx?f=255&MSPPError=-2147217396 is the file level version of defrag.exe.. Both of these attempt using a best effort try and make fragmented files contigous