Space Monitoring – A Tale of Seven Wonders – Wonder 5

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.

wmic volume where drivetype=3 get caption, freespace, capacity, label

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.

DECLARE @xpCmd NVARCHAR(4000)
DECLARE @isclustered INT = 0

SET NOCOUNT ON

IF EXISTS(SELECT 1 FROM master.sys.dm_os_cluster_nodes)
       SET @isclustered = 1

IF (OBJECT_ID('tempdb..#temp') is not null)         
    DROP TABLE #temp         

CREATE TABLE #temp (sample VARCHAR(max))         

IF (OBJECT_ID('tempdb..#temp2') is not null)         
    DROP TABLE #temp2         

CREATE TABLE #temp2 ( 
       [SERVER] SYSNAME 
    ,driveletter VARCHAR(max)         
    ,total_mb BIGINT         
    ,freespace BIGINT         
    ,label VARCHAR(max)         
    )         

SET @xpCmd = 'wmic volume where drivetype=3 get caption, freespace, capacity, label' 

INSERT INTO #temp  
EXEC xp_cmdshell @xpCmd 

DECLARE @excluded VARCHAR(max)         
    ,@str1 VARCHAR(max)         
    ,@str2 VARCHAR(max)         
    ,@str3 VARCHAR(max)         
    ,@str4 VARCHAR(max)         

DECLARE @idx INT         
    ,@i INT         

DECLARE @slice VARCHAR(max)         

SELECT @idx = 1         

DELETE 
FROM #temp         
WHERE sample LIKE (         
            SELECT TOP 1 sample         
            FROM #temp         
            ) AND len(RTRIM(ltrim(sample)))>=1 OR len(RTRIM(ltrim(sample))) IS NULL  
OR len(RTRIM(ltrim(sample))) = 1  
OR sample like '%----------------%'  
OR sample like 'Capacity%'  
OR sample = 'NULL'  
OR sample LIKE '% rows affected%' 
OR sample LIKE '%system reserved%' 

SELECT @i = COUNT(*)         
FROM #temp         

WHILE (@i <>0)         
BEGIN             

       SELECT TOP 1 @excluded = ltrim(rtrim(sample)) 
    FROM #temp         


    SET @idx = charindex(' ', @excluded)         

    IF @idx != 0         
    BEGIN         
            SET @slice = left(@excluded, @idx - 1)         
            SET @str1 = @slice         
            SET @slice = ''         
    END         

    SET @excluded = right(@excluded, len(@excluded) - @idx)         
    SET @excluded = rtrim(ltrim(@excluded))         


    SET @idx = charindex(' ', @excluded)         

    IF @idx != 0         
    BEGIN         
            SET @slice = left(@excluded, @idx - 1)         
            SET @str2 = @slice         
            SET @slice = ''         
    END         

    SET @excluded = right(@excluded, len(@excluded) - @idx)         
    SET @excluded = rtrim(ltrim(@excluded))         


    SET @idx = charindex(' ', @excluded)         

    IF @idx != 0         
    BEGIN         
            SET @slice = left(@excluded, @idx - 1)         
            SET @str3 = @slice         
            SET @slice = ''         
    END         

    SET @excluded = right(@excluded, len(@excluded) - @idx)         
    SET @excluded = rtrim(ltrim(@excluded))         


    SET @str4 = @excluded         
    SET @excluded = rtrim(ltrim(@excluded))                             


    INSERT INTO #temp2
    SELECT @@SERVERNAME         
            ,@str2         
            ,cast(@str1 AS BIGINT) / 1024 / 1024         
            ,cast(@str3 AS BIGINT) / 1024 / 1024         
            ,@str4       

    DELETE         
    FROM #temp         
    WHERE sample LIKE (         
                SELECT TOP 1 sample         
                FROM #temp         
                );         

    SET @i = @i - 1         

END

IF (@isclustered = 1) 
BEGIN 
             SELECT
                    server,
                    driveletter,
                    total_mb AS DriveSize_MB,
                    freespace AS FreeSize_MB,
                    label,
                    (freespace*100/total_mb) AS percent_free
             FROM #temp2
             WHERE SUBSTRING(driveletter,1,1) in    
                    (SELECT DriveName FROM sys.dm_io_cluster_shared_drives)
             ORDER BY percent_free
END 
ELSE
             SELECT
                    server,
                    driveletter,
                    total_mb AS DriveSize_MB,
                    freespace AS FreeSize_MB,
                    label,
                    (freespace*100/total_mb) AS percent_free
             FROM #temp2
             ORDER BY percent_free       
SET NOCOUNT OFF

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,
Manu

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *