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.
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\DriveInfo.DLL 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 = Environment.MachineName
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 compile the code library and compile your library.
‘ csc /target:library /out:C:\FileShare\SQLTools\DriveInfo.DLL C:\FileShare\SQLTools\DriveInfo.vb
Installing Code:
/* Please follow the instructions to install extended 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 – Enabling TRUSTWORTHY database option ON by running following command.
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 creat xp_driveinfo extended procedure.
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[xp_driveinfo]‘) AND typein (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
