HOST: AVAILABLE DISK SPACE FOR SQL DATABASE

Operating SystemWindows

Symptoms : Free disk space on the designated storage device has fallen below the threshold.

Impact : High

Maintaining sufficient storage space is a critical factor in proactive prevention of sudden problems. Examples of severe impact are where data files cannot grow when full. Result – users can’t add new data. In cases where backup procedure is failing, transaction logs may be filling up the drive. As a result, system cannot modify any data.

Expected behavior :

There is no standard metric for free space. AimBetter performance monitor displays free space on all system disks and provides statistics on total storage space on each drive, free space, read/write response times and total busy time. Based on our experience, no less than 20% of combined database files/logs should be free on each drive. This alert notifies you of a possible danger.

Possible causes

Backup failure    Priority : High
Overall database and log size is highly dependent on performance of regular backups
Recommended action :
See our knowledgebase entry regarding backups here.

Data growth    Priority : Medium
Recommended action :
SQL imposes no absolute limits on file growth beyond physical disk size. For this reason, use past performance together with current behavior to maintain enough free space to handle all possible cases. Working from the AimBetter resource monitor for disks, you can track the disks with the largest data storage, and locate folders on these disks which contain the highest volume files. By working in this ‘drill-down’ method, anomalies requiring action should be identifiable quickly. Refer here for guidance on causes of issues caused by log growth.
Transaction log growth may also be the result of high levels of user activity, in which case it is an expected behavior. In cases of abnormal growth not generated by such activity, the causes can vary. A primary cause is failure of backups. In the case of tempDb, these issues are mostly caused by exceptional queries, or incorrect coding.

Large volume of data written to tempdb or pritempdb database    Priority : Medium
Queries that run with a large number of iterations, or are in a state of block or deadlock, will write large volumes to logs until the can commit.
Recommended action :
Investigate current queries from the AimBetter QAnalyzer console, and until resolved, physically increase available free space by deleting unnecessary files, or moving other files to a different drive.

Background

Our recommendation is that free space should be at least 20% of disk total capacity. If you run out of disk space for your database files, you become extremely limited in what you can do on this server.
While there are many reasons for file growth, some of which are normal and expected, several conditions can lead to abnormal conditions and need attention before seriously inhibiting normal activity. These include :

  • Backup failure – see our detailed explanation of cause and effect of this here. Use AimBetter console to look at the application log for details of backup activity
  • In case of SQL unexpected data growth, use AimBetter SQL trace facility to pinpoint the reason for this
  • Transaction and tempDb logs can grow quickly. See our detailed explanation of cause and effect of this here.

    Learn more how you can solve IT systems performance issues faster.



    Share with friends:

    Testimonials:

    FEATURED POSTS

    Menu
    Skip to content