SQLCLR function to return free space for all drives on a server

By: Andy Novick   |   Updated: 2010-04-16   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | > Functions User Defined UDF

Problem

When looking for impending problems due to lack of disk space it's necessary to know how much space is available on each drive. There have been other tips written about how to do this, but in this tip I show you a way this can be done using SQLCLR.

Solution

The .Net framework provides this information very readily through the DriveInfo object, which can be invoked and the information extracted and returned using a SQLCLR Tabled Valued Function.  Table Valued Functions (TVFs) are SQLCLR routines that return multiple rows of multi-column output. This article shows how they are implemented.

Before going too far it should be noted that there are other solutions to the problem of finding the amount of free space on each drive.  In particular, if you're willing to use undocumented system external stored procedures you can use the sys.xp_fixeddrives. Use of that procedure is used in the article Determine Free Disk Space in SQL Server with T-SQL Code. Of course, xp_fixeddrives is going to query Windows to get the information it returns.

The .Net framework exposes the functionality of the Windows operating system and the System.IO.DriveInfo class is the one responsible for information on drives of all types.  It's static method GetDrives returns an array of DriveInfo objects and each DriveInfo object has the details on one of the drives.  GetDrives returns information on all drives that are connected to the system whether they're fixed disks, network drives, full or empty CDRoms.

Most systems will have multiple drives and there are several items of information available for each drive.  This combines to make the TVF an ideal mechanism for exposing drive information.  Each drive will be represented by a row.  Each row has several columns of information such as the drive letter, total megabytes, free megabytes, volume label, etc.

To create a TVF, create a new Visual Studio 2008 project.  Choose either a C# SQL Server Project  or a VB.Net SQL Server Project in the Database Project\SQL CLR section.  It's also possible to code SQLCLR routines in manged C++, but there isn't a built-in project type for C++ so you'll have to code and deploy without the aid of a template.  This example creates a C# CLR function.

Next Visual Studio asks for a Database Reference.  Pick a reference to the database where you want to create the function or Add a New Reference for a database that doesn't yet have a reference.  Here's the dialog that is shown as I select a reference to the ns_lib database on my laptop:

Visual Studio creates a project with only a dummy test script called Test.sql.  Add the file for the function by right clicking on the project in Solution Explorer and selecting Add -> User-Defined Function.  Alternatively you can use the Visual Studio Project Menu and choose Add User-Defined Function.  You'll see this dialog:

Give the function the name you want, in this case drive_info, and press the Add button.  A partial class named UserDefinedFunctions is added to your project.  The name of the class doesn't matter.  If it's a partial class, where are the other parts?  They're in any other files that are also members of the same UserDefinedFunction class in the same assembly.  I just leave them named the way Visual Studio does.   The function that the C# SQL Server Project template creates is for a static user-defined function and looks like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString drive_info()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

The first step is to modify the SqlFunction attribute on the drive_info function.  The attribute name remains SqlFunction, but it has two parameters.

For a TVF the FillRowMethodName and the TableDefintion are required and those are the only paramaters used in creating drive_info.  The FillRowMethod name in the example that follows is "FillRow".  By convention, that's the name used by most TVFs.  TableDefinition is given in T-SQL table definition syntax.  The table definition for the drive_info function is:

TableDefinition ="letter nchar(1),total_mb bigint null," +
                 "free_mb bigint null, format nvarchar(32) null," +
                 "type nvarchar(32) null," +
                 "volume_label nvarchar(20) null")]

To write the function, first bring in the necessary references.  For drive_info the System.IO and System.Collections namespaces are referenced with using statements. 

The body of drive_info is almost trivial.  The function must return an IEnumerable, which includes arrays.  drive_info returns the array of DriveInfo objects returned by the GetDrives static method.  It doesn't get much easier than that.

The FillRow method takes an input parameter that's an Object and has one out parameter for each output column.  Of course, the output columns must match the definiton given in the TableDefinition parameter of the SqlFunction attribute on the drive_info function.  SQL Server will do type conversions as best it can but it can't handle all mismatches.  Of particular importance is the nullability of the types that are returned.  Notice that the total_mb and the free_mb parameters below are declared with the type SqlInt64.  Doing so allows them to be nullable.  Had they been declared as CLR Int64's SQL Server would handle the data without problem but there would be no way to pass a null value.  It's best to use the SQL types when nullability is a possibility.  drive_info has some parameters, such as letter, format, and volume_label to illustrate that CLR types can be returned and handled correctly.

Here's the complete function:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO; /// to bring in the DriveInfo class
using System.Collections; /// bring in the IEnumberable defintion
public partial class UserDefinedFunctions
{
    /// <summary>
    /// returns informatoin on alal drives in the system
    /// </summary>
    /// <returns></returns>
    [SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition ="letter nchar(1),total_mb bigint null," +
                         "free_mb bigint null,format nvarchar(32) null," +
                         "type nvarchar(32) null," +
                         "volume_label nvarchar(20) null")]
    public static IEnumerable drive_info()
    {
        
        return System.IO.DriveInfo.GetDrives();
    }
    public static void FillRow(Object obj
                             , out char letter
                             , out SqlInt64 total_mb
                             , out SqlInt64 free_mb
                             , out string format
                             , out string type
                             , out string volume_label)
    {
        DriveInfo drive = (DriveInfo)obj;
        letter = drive.Name[0];
        type = drive.DriveType.ToString();
        // Some drives might be empty and thus not IsReady
        if (drive.IsReady)
        {   total_mb = new SqlInt64(drive.TotalSize / 1048576);
            free_mb = new SqlInt64(drive.TotalFreeSpace / 1048576);
            format = drive.DriveFormat;
            volume_label = drive.VolumeLabel;
        }
        else
        {   total_mb = new SqlInt64();
            free_mb = new SqlInt64();
            format = null;
            volume_label = null;
        }
    }
};

The body of the FillRow method starts out by casting the Object into the type of object that's expected by the function, a DriveInfo.  The rest of the function uses the methods of DriveInfo to extract items of information to return as the columns.   CDRom drives might not be holding a disk and therefore wouldn't be able to return most information.  Rather than handle any errors from an empty drive the IsReady function is tested and null values returned.  The FillRow method should assign something to each parameter, even if it's the value null.

The GetDrives method is considered "UnSafe" code by SQL Server and before the project can be used the assembly that's going to be produced must be marked with SQL Server's Unsafe attribute.  Do this in the databases tab of the project properties as shown next by setting the Permission Level.

Build and Deploy the function with Visual Studio's Build -> Deploy menu.

Finally, to use drive_info, invoke it from SSMS:

select * from dbo.drive_info()

On my laptop the results are:

letter total_mb  free_mb  format  type       volume_label
------ --------- -------- ------- ---------- ------------
C      244095    75851    NTFS    Fixed      
D      NULL      NULL     NULL    CDRom      NULL
E      95393     59159    NTFS    Fixed      100GMobile
H      76319     76227    NTFS    Removable  New Volume
(4 row(s) affected)

Next Steps

• Build the drive_info TVF on your system.
• Incorporate the information into your drive space early warning system such as the one described in Capacity Planning for SQL Server 2000 Database Storage.
• Consider using a TVF instead of a stored procedure when you must return system information.
• Download the C# code here

About the author

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips