CLR store procedure to get drive and mount point free space information

As we know xp_fixeddrives can only be use to retrieve normal fixed drives space information. It cannot be used to retrieve information about the 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.

I have written and implemented the following CLR (.NET Framework) code to get the disk and mount point space information.  In this blog post I will show you the code i.e. written in VB.NET and the implementation of this custom CLR procedure.

Demo

Open Microsoft Visual Studio and create SQL Stored Procedure project. Choose VB.NET as a code language.  Copy the VB.NET code below and then save the file and follow the instructions below to compile the code:

'csc /target:library /out:C:\FileShare\SQLTools\DriveInfoDLL C:\FileShare\SQLTools\DriveInfo.vb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub DriveInfo()
        Dim ServerName As String
        ServerName = EnvironmentMachineName
        Dim pcc As New PerformanceCounterCategory(“LogicalDisk”, ServerName)
        Dim record As 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)
        For Each instanceName As String In pcc.GetInstanceNames()
        Dim pcPercentFree As New PerformanceCounter (“LogicalDisk”
                   , ”% Free Space”, instanceName, ServerName)
        Dim pcFreeMbytes As New PerformanceCounter(“LogicalDisk”, ”Free Megabytes”
                   , instanceName, ServerName)
            Dim percentfree As Single = pcPercentFree.NextValue()
            Dim freespace As Single = pcFreeMbytes.NextValue()
            Dim capacity As Single = (freespace * 100) / percentfree
            Dim usedspace As Single = capacity – freespace
            If instanceName <> ”_Total” Then
                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)
            End If
        Next
        SqlContext.Pipe.SendResultsEnd()
    End Sub
End Class

Compile Code:

Open Visual Studio command prompt, and then compile your code as follow:

csc /target:library /out:C:\FileShare\SQLTools\DriveInfo.DLL C:\FileShare\SQLTools\DriveInfo.vb

Installing Code:

Please follow the instructions below to install CLR stored procedure (xp_driveinfo):

-- STEP 1 – Copy (DriveInfo.dll) onto C: drive the server where you want install (xp_driveinfo)
-- STEP 2 – Connect to the SQL Instance where you are installing the procedure and run the following command to enable CLR feature on the instance.
USE [master]
GO

sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'clr enabled'
	,1;
GO

RECONFIGURE;
GO

--STEP 3 – Execute to set TRUSTWORTHY database option for master database to ON
ALTER DATABASE [master]

SET TRUSTWORTHY ON;
GO

--STEP 4 – Creating the Assembly for DriveInfo.dll
USE [master]
GO

CREATE ASSEMBLY DriveInfo
FROM 'C:\DriveInfo.dll' WITH PERMISSION_SET = UNSAFE
GO

--STEP 5 – Execute the following command to create xp_driveinfo extended procedure.
USE [master]
GO

IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[xp_driveinfo]')
			AND type IN (
				N'P'
				,N'PC'
				)
		)
	DROP PROCEDURE [dbo].[xp_driveinfo]
GO

CREATE PROCEDURE [dbo].[xp_driveinfo]
	WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DriveInfo].[StoredProcedures].[DriveInfo]
GO
About these ads

4 thoughts on “CLR store procedure to get drive and mount point free space information

  1. The CLR code works great, but the CREATE PROCEDURE [dbo].[xp_driveinfo] statement fails with the following message: Could not find Type ‘StoredProcedures’ in assembly ‘DriveInfo’

    Like this

    • Hi Michael, Looks like DLL is not compiled properly. Compile the DLL with the following command:

      csc /target:library /out:C:\FileShare\SQLTools\DriveInfo.DLL C:\FileShare\SQLTools\DriveInfo.

      Once the DLL is compiled create the ASSEMBLY and then CREATE the procedure.

      This is working fine for me. Ensure you follow the above steps correctly.

      Like this

  2. i ran deployed the sp. While running it on my laptop on local sql express i see following error A .NET Framework error occurred during execution of user-defined routine or aggregate “usp_MonitorMountPoint”:
    System.FormatException: Input string was not in a correct format.
    System.FormatException:
    at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
    at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
    at System.Int32.Parse(String s, IFormatProvider provider)

    Please help.

    Like this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s