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