In the first two parts, (Part 1, Part 2), of the Getting Drive Info series the techniques to gather drive info with methods that will work on SQL Server 2000 were presented. Now it is time to move on to look at the options that the newer versions of SQL Server can use. In this article the drive info will be retrieved via the CLR. Andy Novick (Blog) wrote the code to accomplish this in his article, CLR Function to return free space for all drives on a server, on MSSQLTips.com. Security and environment set up were not covered in this article. Rather than reinvent the wheel, the code from the referenced article will be used. I will discuss security and the setup I created to run it .

The first attempt was to build and deploy to the master database. This resulted in the following error:

Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate “drive_info”:

System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.Directory.GetLogicalDrives()

at System.IO.DriveInfo.GetDrives()

at UserDefinedFunctions.drive_info()

After getting the above error and not really understanding the ramifications of an unsafe assembly, I decide it was time to do some reading. I found that the book, A Developers Guide to SQL Server 2005, had an excellent discussion of the CLR and the different assembly security types. Now I was ready to create an environment for this function.

Since this was a function for DBAs to use for system monitoring, I created a new database named DBA_Tools and set the owner to sa. In order to let an Unsafe assembly execute, the TRUSTWORTHY option needs to be enabled. Turn on Trustworthy with this command.

ALTER DATABASE DBA_Tools SET TRUSTWORTHY ON

Make sure that the CLR server option is enabled, otherwise you can’t execute the CLR function. This can be accomplished with this script.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

One thing that I did notice is having the CLR server option enabled or disabled had no impact on deploying the function. This is a great feature if you typically don’t want the CLR enabled all of the time. The CLR can be enabled by the script that runs drive_info() function and then be disabled immediately after.

Moving on to the Visual Studio setup, make sure the connection in the CLR database project points to the DBA_Tools database. Set the Assembly name property on the Application tab of the Solution property page to something more appropriate.

The CLR function performed as advertised after setting everything up correctly. The function was built in both Visual Studio 2005 and 2008 and deployed to the corresponding versions of SQL Server. The setup worked on both SQL Server 2005 and 2008. Here is a sample output.

Utilizing the table, DriveInfo, that was used in the previous installments, the script to populate it would be:

INSERT INTO DriveInfo
SELECT letter   AS Drive
     , total_mb AS DriveSize
     , free_mb  AS FreeSpace
  FROM drive_info()
 WHERE type = 'Fixed'

Although this solution works well enough, there is some risk with this approach. Based on how an Unsafe assembly is handled with in the SQLOS, there is a risk for memory leaks when an unhandled exception occurs. If a solution can be created that uses alternative SQL Server features rather than an Unsafe CLR assembly, I would choose the alternative. Speaking of alternatives, that where the next installments come in. They will cover using SSIS to get the drive info via the Script Component and WMI.