Ramblings of a DBA

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 = new PerformanceCounterCategory("LogicalDisk", serverName);

        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
            new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6));

        SqlContext.Pipe.SendResultsStart(record);

        foreach (string instanceName in pcc.GetInstanceNames())
        {
            PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
            PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);

            float percentfree = pcPercentFree.NextValue();
            float freespace = pcFreeMbytes.NextValue();
            float capacity = (freespace * 100) / percentfree;
            float usedspace = capacity - freespace;

            if (instanceName != "_Total")
            {
                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 = ''

EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'


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

posted @ Tuesday, December 18, 2007 3:45 PM | Feedback (4)