Tara Kizer Blog

Tara Kizer

CLR stored procedure to get disk space information

I've seen lots of tools/scripts to get disk space information about a particular SQL Server, but none of them include the information about mount points.  Mount points are now supported in SQL Server 2005.  We implemented them on our production clusters and therefore needed a way to monitor them.  We are now monitoring them with MOM via a Veritas management pack, but before that was implemented we used a CLR stored procedure that I wrote.  It is written in C#. 

The code allows you to get disk space information for the local server or for remote servers.  You just need to pass the hostname to the stored procedure.

NOTE: I didn't get a chance to add error handling to this.  I'd love to hear your suggestions on how better to write this, but please be respectful.  I'm only a DBA! 

C# code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void isp_DiskSpace(string serverName) { if (serverName == "") { serverName = Environment.MachineName; }

    PerformanceCounterCategory pcc = <span class="kwrd">new</span> PerformanceCounterCategory(<span class="str">&#34;LogicalDisk&#34;</span>, serverName);

    SqlDataRecord record = <span class="kwrd">new</span> SqlDataRecord(
        <span class="kwrd">new</span> SqlMetaData(<span class="str">&#34;Drive/MountPoint&#34;</span>, SqlDbType.NVarChar, 256),
        <span class="kwrd">new</span> SqlMetaData(<span class="str">&#34;Capacity (MB)&#34;</span>, SqlDbType.VarChar, 256),
        <span class="kwrd">new</span> SqlMetaData(<span class="str">&#34;Used Space (MB)&#34;</span>, SqlDbType.VarChar, 256),
        <span class="kwrd">new</span> SqlMetaData(<span class="str">&#34;Free Space (MB)&#34;</span>, SqlDbType.VarChar, 256),
        <span class="kwrd">new</span> SqlMetaData(<span class="str">&#34;Percent Free Space&#34;</span>, SqlDbType.VarChar, 6));

    SqlContext.Pipe.SendResultsStart(record);

    <span class="kwrd">foreach</span> (<span class="kwrd">string</span> instanceName <span class="kwrd">in</span> pcc.GetInstanceNames())
    {
        PerformanceCounter pcPercentFree = <span class="kwrd">new</span> PerformanceCounter(<span class="str">&#34;LogicalDisk&#34;</span>, <span class="str">&#34;% Free Space&#34;</span>, instanceName, serverName);
        PerformanceCounter pcFreeMbytes = <span class="kwrd">new</span> PerformanceCounter(<span class="str">&#34;LogicalDisk&#34;</span>, <span class="str">&#34;Free Megabytes&#34;</span>, instanceName, serverName);

        <span class="kwrd">float</span> percentfree = pcPercentFree.NextValue();
        <span class="kwrd">float</span> freespace = pcFreeMbytes.NextValue();
        <span class="kwrd">float</span> capacity = (freespace * 100) / percentfree;
        <span class="kwrd">float</span> usedspace = capacity - freespace;

        <span class="kwrd">if</span> (instanceName != <span class="str">&#34;_Total&#34;</span>)
        {
            record.SetSqlString(0, instanceName);
            record.SetSqlString(1, capacity.ToString());
            record.SetSqlString(2, usedspace.ToString());
            record.SetSqlString(3, freespace.ToString());
            record.SetSqlString(4, percentfree.ToString());
            SqlContext.Pipe.SendResultsRow(record);
        }
    }
    SqlContext.Pipe.SendResultsEnd();
}

};

Once you've compiled the code into a DLL, run the below to get it setup inside SQL Server.

T-SQL code:

USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE

ALTER DATABASE Admin SET TRUSTWORTHY ON GO

USE Admin GO

CREATE ASSEMBLY DiskSpace FROM 'C:\SQLTools\DiskSpace.dll' WITH PERMISSION_SET = UNSAFE GO

CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace GO

Example calls:

--local server
EXEC dbo.isp_DiskSpace @serverName = ''
or
EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'

–remote server EXEC dbo.isp_DiskSpace @serverName = 'RemoteServer'

Legacy Comments


Jon
2007-12-18
re: CLR stored procedure to get disk space information
Very nice I can definately use this. One minor thing when checking strings (the C# code) avoid using string == "".
Its easier to use .Empty or the .Length property of a string. When you do use == "", "" is an actual object ( a string ) while String.Empty does not create an object. As far as speed its very very minor (not noticeable) its just a better way to handle strings. .Length is probably faster then both == "" and String.Empty.

Peso
2007-12-19
re: CLR stored procedure to get disk space information
Well done Tara.

Now for us who don't have c#, can you compile the DLL and post a link for download?

Peso
2007-12-19
re: CLR stored procedure to get disk space information
Some improvements

1) Change the line

public static void isp_DiskSpace(string serverName)

to

public static void isp_DiskSpace(string serverName = "")

to default the servername to empty, ie make the current server the default servername if no servername is supplied.

2) Change the metadate lines VARCHAR to INTEGER.



Mladen
2007-12-19
re: CLR stored procedure to get disk space information
public static void isp_DiskSpace(string serverName = "") is not valid syntax in c#
disk size is a decimal so it should probably be SqlDbType.Decimal and not varchar or int