Hello Geeks,

Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.

Last week I have blogged about the max server memory setting and why it is important to configure the setting. This week I will blog about the daunting task of space monitoring for DBAs.

When was the last time you were proactively informed about a drive running out of space? Are all your drives monitored? What should be the ideal thresholds? When was the last time you as a DBA were involved in setting up the space monitoring alerts? A lot of questions which are really important.

To add to already existing challenges, as hosting multiple instances of SQL server on single machine or cluster has increased. With the limitation on drive letters, usage of mount points has increased. With simple commands in SQL server like xp_fixeddrives, it is misleading and hard to monitor the free space available in mount points.

If you have a third party tool like SCOM, it is easier to set a universal policy to monitor all the drives. But it is highly complicated to set threshold for individual drives? For example, for a drive of 100 GB holding log files of size 50 GB you can set the threshold to less than 10% free space for alerts. But for a drive of 2 TB holding data files of 1.85 TB you can wait till 50 GB is left rather than 10% which is 200 GB.

There are further considerations like the auto growth setting of files in the drive, etc. The thresholds should be customizable based on the usage of the drive. The worst case I have seen is the threshold set to 5 MB as there have been many alerts. The tool did not have an option to set individual alerts for each drive. It was a bulk setting across all drives. You do not want to have such monitoring on your database environment.

So, let us design a solution. These are the requirements so far.

  1. Monitors mount points.
  2. Have customizable thresholds (e.g.: drive < 50 GB raise alert for 5 GB, drive between 50 GB and 500 GB raise alert for 10% etc.).
  3. Have exceptions if we want to skip a drive or set it on a different threshold than rest.

With this requirement I have decided to use wmi query to get the drive free space details including mount points.

Now, it just needs some filtering to figure out if it is a cluster or a non-clustered SQL instance. This will help to get only the drive details related to the SQL instance. So the first part of the requirement to collect the disk space related data is as follows.

I will cover the second part of the requirement in the blog next week. Till I blog the second part which actually sends the alert based on thresholds, use this script to monitor the size of drives including mount points in your environment and share the feedback.

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook