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 = ''

EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'

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