none
high logical fragmentation on disk but the disk is 99% empty

    Question

  • hi, I'm not sure if its the right forum and if it is not the right one, please point me to the right forum.

    I have a server with os 2012 R2 & SQL server 2012, used for small application with a little number of writes, so temp DB is very small and the transaction log is small also... 

    the Tlog located in a different drive from the Data and the Temp DB is also in a different drive...

    lately, I started to get warnings and errors from scom about 100% fragmentation in the logical disk, and when I entered the server I saw that drive T (TempDB) and drive L (Log) has 100% fragmentation in the "optimizing hard-drives", but when I looked inside of them I saw that the HD are 99% empty (for example from 10GB, only 100mb in use).

    can someone explain to me what does this fragmentation means and how it is on 100% if the drive is 99 empty?

    and I would like to know how to treat fragmentation on SQL server's drives, can it affect the SQL? do I need to ignore it? and what about the task of Microsoft doing defrag in the task scheduler, if it is not affecting the SQL, so can I do it also without fear of destroying the SQL?

    • Edited by HBShare Saturday, January 12, 2019 6:54 PM
    Saturday, January 12, 2019 6:53 PM

All replies

  • If you are talking about fragmentation in the file system and why it can 100% on an empty disk, that sounds like a question for a Windows forum.

    Yes, fragmentation in the file system can affect SQL Server. If a data file or a log file is fragmented, this can degrade performance, although this may apply only to spinning disks. SSDs is a different world. But fragmentation can also cause problem when SQL Server wants to grow a file, or create the hidden snapshot for DBCC. If you ever see OS error 665 when working with SQL Server, you know that your disks needs defragmentation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 12, 2019 7:40 PM
  • and if its a virtual server (storage from datastore, san)?
    and doing defrag can affect the SQL?
    Saturday, January 12, 2019 9:34 PM
  • I guess if it is a virtual server, there are even more possibilities for fragmentation. You get fragmentation on the logical disk inside the VM, but the physical disk on the VM host can also be fragmented. Which can cause performance issues for the VM and anything that is running on it.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 12, 2019 10:37 PM
  • how can I check if its VM related?
    because I did not saw any errors in the VSphere console... 
    how can I troubleshoot it?
    Sunday, January 13, 2019 12:06 AM
  • I see you have got some answers in the Windows Forum

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 13, 2019 6:37 AM
    Answerer
  • Please post the SCOM error. Given your drives are so empty it sounds like we might be talking SQL server fragmentation but this isn't clear from the information you have provided.

    Also, if I was you, I would run a read-only <g class="gr_ gr_235 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="235" id="235">chkdsk</g> on each of the drives to make sure there is nothing weird going on. *Warning* do not fix any problems without first ensuring you have full and log backups of every database and consult any onsite professionals before doing so.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master | Microsoft Data Platform MVP
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it has been helpful.

    Interested in attending a free training? Why not attend SQLSaturday Cambridge and SharePoint Saturday Cambridge? And why not check out my Ultimate Training Resources Page?

    Monday, January 14, 2019 9:28 AM
  • the storage of the VM is not from the VM's host, it is from the NetApp storage system
    Wednesday, January 16, 2019 10:03 PM
  • not any useful one...
    Wednesday, January 16, 2019 10:05 PM
  • I did consult with an SQL Server professional but he said that the logical fragmentation on the disk is not related to the SQL Server (i also checked the indexes and they are fine and not fragmented) and he actually showed me the Microsoft task that does defrag every week...

    and why do I need to consult a professional? the windows task scheduler runs defrag task every week and it does no damage... 


    I cannot enter my environment right now but I will update this thread with the error...

    Wednesday, January 16, 2019 10:11 PM