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

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.

$either a C# SQL Server Project or a VB.Net SQL Server Project in the Database Project\SQL CLR section$

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:

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:

$Add the file for the function by right clicking on the project in Solution Explorer and selecting Add -> User-Defined Function” src=”/wp-content/images-tips/1986_add%20new%20item%20dialog%20for%20a%20sql%20clr%20user%20defined%20function.JPG” width=713 height=410></DIV>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:<DIV class=codediv><PRE>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”); } };</PRE></DIV>The first step is to modify the SqlFunction attribute on the drive_info function. The attribute name remains SqlFunction, but it has two parameters.<DIV align=center> <TABLE id=table25 border=1 cellSpacing=0 cellPadding=4 width=600> <TBODY> <TR> <TD bgColor=#c0c0c0 width=90> Parameter</TD> <TD bgColor=#c0c0c0>Description</TD></TR> <TR> <TD width=90>FillRowMethodName</TD> <TD>the name of a static void method that will return the contents of each row.</TD></TR> <TR> <TD width=90>TableDefinition</TD> <TD>the T-SQL table definition of the dataset that is going to be returned.</TD></TR> <TR> <TD width=90>Name</TD> <TD>used to give the T-SQL Function a different name than the CLR function.</TD></TR> <TR> <TD width=90>DataAccess</TD> <TD>Tells SQL Server if the function access data from the database.</TD></TR> <TR> <TD width=90>SystemDataAccess</TD> <TD>Tells SQL Server if the function access data from a system catalog or DMV.</TD></TR></TBODY></TABLE></DIV>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:<DIV class=codediv><PRE>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”)]</PRE></DIV>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:<DIV class=codediv><PRE>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; } } }; </PRE></DIV>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.<DIV class=imageborder><IMG border=1 alt=$

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

Andy Novick

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Loadfest” event held in Waltham, MA.

MSSQLTips Awards: Trendsetter (25+ tips) – 2014

Leave a ReplyCancel Reply