Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Get drive & mount points space information in SQL Server 2005 by using CLR Stored Procedure?

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

Comments

Leave a comment on the original post [basitaalishan.wordpress.com, opens in a new window]

Loading comments...