Managing Active Directory does not always have to be complicated. If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
how often do you optimize the drive? default is weekly if enabled.
read this article as the principles are universal
set the autogrow sizes appropriately the DBA will know about this.
http://www.hasmug.com/wp-content/uploads/2012/10/07-201210-Oct-SQL-Server-Optimization-and-Best-Practices-for-System-Center-Administrators-Kevin-Holman.pdf
read this article as the principles are universal
set the autogrow sizes appropriately the DBA will know about this.
http://www.hasmug.com/wp-content/uploads/2012/10/07-201210-Oct-SQL-Server-Optimization-and-Best-Practices-for-System-Center-Administrators-Kevin-Holman.pdf
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
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.
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
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
please, provide instructions step by step to resolve this issue