Haven't you ever been frustrated with the output of xp_fixeddrives? Ever felt like saying 'gosh I wish I could get the total size of each drive from this extended stored procedure, so I'd be able to compute how much space is used so far'. Well now you have the SQLCLR in SQL Server 2005 and later, so no excuses. In this article, we'll discuss a way to display the host's fixed drives characteristics in a SQLCLR table- valued function.
As a UNIX-guy, I like df. I though it would be great to have a table-valued function in SQL Server that could just return what df returns:
$ df -k
Filesystem 1K-blocks Used Available Used% Mounted on
/dev/sda2 1951808 227352 1724456 12% /
tmpfs 2022556 0 2022556 0% /lib/init/rw
udev 10240 68 10172 1% /dev
tmpfs 2022556 0 2022556 0% /dev/shm
/dev/sda6 63229872 21252420 41977452 34% /opt
/dev/sda3 1951808 545680 1406128 28% /usr
/dev/sda5 1951744 777980 1173764 40% /var
/dev/sdb1 142725048 45940328 96784720 33% /data2
I'd like to see such results in a table-valued format:
- The 'Filesystem' information can be viewed as the drive letter on Windows.
- The '1K-blocks' refers to the total size of the drive
- 'Used' to the space used on the drive.
- 'Available' to the remaining free space in the drive.
- And finally, 'Used%" reflects the percentage of space used.
The fn_fixeddrives.dll source code:
I took a nice cup of coffee, opened my copy of Visual Studio and after about one hour and a half trying various approaches, I ended up with a small piece of code. In case you are not familiar with c# and SQLCLR coding, let me explain each part of it.
First, we need to tell the linker, which is a part of the c# compiler, what external references we'll use in the code. We'll put the keyword using in front of each reference:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;
Most of SQLCLR code uses references like System.Data... and Microsoft.SqlServer... These references contain libraries and methods to manipulate SQL Server objects (tables, views, etc...). The 2 key system references that we are adding here are System.IO, which will provide access to the disks and drives characteristics, and System.Collections that will provide the ArrayList object type that we use in the code.
From here, we can declare the main class to the compiler:
public partial class cFixedDrives
{
static DriveInfo[] _t_drvlist; [Microsoft.SqlServer.Server.SqlFunction
(
FillRowMethodName = "_f_fill",
TableDefinition = "Drive nvarchar(3), SizeMb bigint, UsedMb bigint, AvailableMb bigint, UsePct bigint"
)
] // Main
public static IEnumerable fn_fixeddrives()
{
_t_drvlist = DriveInfo.GetDrives();
ArrayList _t_index = new ArrayList();
int i = 0; foreach (DriveInfo _drv in _t_drvlist)
{
if (("Fixed" == _drv.DriveType.ToString()) &&(_drv.IsReady))
{
_t_index.Add(i);
}
i++;
} return _t_index;
} // _fill
private static void _f_fill(Object _v_Obj, out string _v_drvName,
out long _v_totalsizeMb, out long _v_usedsizeMb,
out long _v_freesizeMb, out long _v_pctused)
{
int _t_row = (int)_v_Obj; _v_drvName = _t_drvlist[_t_row].Name;
_v_totalsizeMb = _t_drvlist[_t_row].TotalSize / 1048576;
_v_usedsizeMb = (_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace) / 1048576;
_v_freesizeMb = _t_drvlist[_t_row].TotalFreeSpace / 1048576;
_v_pctused = ((_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace) * 100 / _t_drvlist[_t_row].TotalSize);
}
};
The class is named cFixedDrives. I like to put some semantics in the object names, so I usually prefix the class name with a 'c'. We'll use the name later when binding the assembly in SQL Server with the dynamic library resulting from the compilation.
We declare a static global variable named _t_drvlist of type DriveInfo, that will store an array of drives returned by the GetDrives() method. It is declared globally (outside of any method definition) so it is available from any block of code in the class. And then we declare the SQLCLR function.
A SQLCLR function is always of type Microsoft.SqlServer.Server.SqlFunction, but because it is also a table-valued function, we need to define some additionnal properties: first, the method name that will be used to fill each row returned by the function (FillRowMethodName ="_f_fill"), and next the table layout returned, with each column name and type.
Next, we enter the main entry point of the SQLCLR function, the fn_fixeddrives() method. It must be of type IENumerable to return a table-valued result. To let you better understand how it interacts with the _f_fill() method, consider figure 1:
fn_fixeddrives() calls the GetDrives() method (1) to retrieve drive properties, and fills the _t_tdrvlist[] array with the results. GetDrives() returns one reference per drive on the system. These drives can be 'Fixed' (physical disk) or 'NonFixed' (DVD or floppy). We only need those which are of type 'Fixed', so instead of passing the array directly to the fill method, we'll use a trick. We'll retrieve every position in the _t_drvlist[] where there is a Fixed and ready drive and build a dynamic array containing these positions (2). It is important to test the IsReady property to avoid the SQLCLR exception handler to raise an IOException error in case the drive is not ready **
This dynamic array is then passed to _f_fill() to seek every fixed drive in _t_tdrvlist[] (3). This is much lighter than passing the entire drive array because we only need to allocate an integer array in the _f_fill() method instead of a new DriveInfo[]array.
Finally, for each element found in _t_drvlist[], the _f_fill() method builds a new row (4), and in the end it returns a whole table containing the list and 'df-like' properties of each fixed drive on our system (5).
I compiled it as a dll in Visual Studio, but you can also use your favorite editor and compile very simply by using the C# compiler like:
DOS> csc /target:library fn_fixeddrives.cs
Declaring the assembly and the SQLCLR function in SQL Server
Unfortunately, the GetDrives() method is considered as UNSAFE by SQL Server, you even won't be able to declare the assembly as EXTERNAL_ACCESS. I guess it is because it brings a system dependency (System.IO), which may not be on the approved list (If you try to add a new reference in your sqlclr project, you won't see System.IO in the list.*). In general, try to avoid allowing UNSAFE assemblies to be loaded in SQL Server process address space without a careful look at the source code.
That said, in our case the code doesn't manipulate any null value thanks to the filter on the 'Fixed' type, and avoids most of the situations where an IOException could be raised by testing whether the drive is ready or not. I believe we can give a try.
We need to allow SQL Server to run clr, and then allow the database to load unsafe assemblies:
sp_configure 'clr enabled', 1
go
reconfigure
go
alter database clrtest set trustworthy on
go
Then we can declare the assembly and the SQLCLR function:
use clrtest
GO
CREATE ASSEMBLY fixeddrives from 'E:\CAPDATA\DEV\CLR\fn_fixeddrives\bin\Debug\fn_fixeddrives.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE FUNCTION fn_fixeddrives ()
RETURNS TABLE ("Drive" nvarchar(3), "Size(Mb)" bigint, "Used(Mb)" bigint, "Available(Mb)" bigint, "Use%" bigint)
AS EXTERNAL NAME fixeddrives.cFixedDrives.fn_fixeddrives
GO
And finally here you go:
select * from fn_fixeddrives()
GO
Drive Size(Mb) Used(Mb) Available(Mb) Use%
---- -------- -------- -------------- -----
C:\39997 27403 12593 68
E:\205244 129893 75350 63
G:\76321 18700 57621 24
V:\60000 30254 29746 50
Conclusion
In this article we have seen how to use SQLCLR to display a table-valued format of the fixed drives characteristics. These results can be coupled with data and transaction log file sizes and max sizes to see how much space is used and left on the drive where they are stored. For example using a CTE and our SQLCLR function:
with DBfiles as (select name, substring(physical_name,1,3) as Drive, size, max_size from AdventureWorks.sys.database_files)
select DBF.name, DBF.size/128 as "Size (Mb)", -- For data files, unlimited max_size = -1 case DBF.max_size when -1 then (F.[Available(Mb)]+size/128) -- For transaction log files, unlimited max_size = 2097152 when 2097152 then (F.[Available(Mb)]+size/128) else DBF.max_size/128 end as "Real Max Size(Mb)"
from DBfiles DBF
inner join fn_fixeddrives() F on F.Drive = DBF.Drive name Size (Mb) Real Max Size(Mb)
-------------------------- ----------- --------------------
GESTIMMO_data01 231 241707
GESTIMMO_log01 241 2097152
I hope you will use this function as much as I do. Having such information in a table-valued format makes it very easy to manipulate and store. Looking forward all of your comments !
David B.
References:
Bob Beauchemin's blog at http://www.sqlskills.com/BLOGS/BOBB/
Kimberly Tripp's white paper on CLR: http://msdn.microsoft.com/en-us/library/cc917671.aspx
SQL Programmability Blog: http://blogs.msdn.com/b/sqlprogrammability
Notes:
* to check which dependency is on the approved list, check Bob Beauchemin's blog post here.
** http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/17/578613.aspx
