Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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 = ''
or
EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'
--remote server EXEC dbo.isp_DiskSpace @serverName = 'RemoteServer'

Print | posted on Tuesday, December 18, 2007 3:45 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# 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.
12/18/2007 6:24 PM | Jon
Gravatar

# 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?
12/19/2007 12:06 AM | Peso
Gravatar

# 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.


12/19/2007 5:31 AM | Peso
Gravatar

# 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
12/19/2007 5:38 AM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET