What is this?

For reference, check

http://www.mssqltips.com/sqlservertip/2623/configuring-volume-mountpoints-on-a-sql-server-2008-failover-cluster-running-on-windows-server-2008/

A LUN can be mounted as a folder, however, traditional tools are unable to see the space utilized inside the mounted point.

To do that, we need to use WMI queries.

Step 1

Save this VBS file on the file system

'Author : Vidhya Sagar
'Modified by: Miguel Quintana
'Date : 01st Feb 2009
'Written for sql-articles.com
'Version 2

On Error Resume Next

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' The WMI class will only exist when there are volumes presentm mounted to a drive or a folder
' therefore, the "on error resume next" becomes mandatory.
	
	Set colDisks = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

' This section deals with volumes mounted to a NTFS folder.
If colDisks.count > 0 Then
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical Disk" & "|" & objDisk.DeviceID
    Next
    Set objWMIService1 = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks1 = objWMIService1.ExecQuery _
        ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")
 
    For Each objDisk1 in colDisks1
    Wscript.Echo mid((objDisk1.Capacity)/1048576,1,8) & "*" & mid((objDisk1.Freespace)/1048576,1,8) & "#" & "MountedDrive" & "|" & mid(objDisk1.Name,1,100)
    Next
Else

' This section deals with drives that are not mounted(such as C:\) and with
' mounted volumes to a drive, not a folder
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
    Set colDisks = objWMIService.ExecQuery _
        ("Select * from Win32_LogicalDisk Where DriveType = 3")
    For Each objDisk in colDisks
    Wscript.Echo mid((objDisk.size)/1048576,1,8) & "*" & mid((objDisk.Freespace)/1048576,1,8) & "#" & "Logical_Disk" & "|" & objDisk.DeviceID
    Next
End if

Step 2, run the following TSQL script:

/*
DESCRIPTION    List space available in each drive. Compatible with mounted folders.
Must use with Win.vbs file
 
CONFIGURATION    See config section to specify the location of the win.vbs file
        SET @VBSPATH='cscript C:\temp\win.vbs'

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000

ISSUES:

 
*/


 
    
SET NOCOUNT ON


-- Check to see if 'Show Advanced Option' is enabled
-- Check to see if 'Ole Automation options' is enabled

 
declare @chkOLE as sql_variant
declare @adv_opt as sql_variant
declare @op_ch1 as int
declare @op_ch2 as int
-- these variables keep track if we changed this options, we'll change them back before finishing
 
set @op_ch1=0
set @op_ch2=0
 
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
 EXEC sp_configure 'show advanced options',1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch1 = 1
end
 
select @chkOLE = value from sys.configurations where name = 'Ole Automation Procedures'
if @chkOLE = 0
begin
 EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE WITH OVERRIDE;
 set @op_ch2 = 1
end
 
 


--  Starting the script


-----******************** CONFIGURATION *****************
DECLARE @VBSPATH VARCHAR(200)
SET @VBSPATH='cscript C:\temp\win.vbs' -- Change the path here

-----******************** ENDS CONFIGURATION *****************
 
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp')
DROP TABLE ##tmp
 
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell @VBSPATH
 
SET ROWCOUNT 3
DELETE ##tmp
 
SET ROWCOUNT 0
 
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME ='##tmp2')
DROP TABLE ##tmp2
 
CREATE TABLE ##tmp2(DriveName VARCHAR(100),DriveDescription VARCHAR(15),TotalDiskSpace_in_MB VARCHAR(10), Freespace_in_MB VARCHAR(10))
INSERT ##tmp2
 
 
SELECT RTRIM(SUBSTRING(diskspace,charindex('|',diskspace)+1,100)),
 SUBSTRING(diskspace,charindex('#',diskspace)+1,charindex('|',diskspace)-charindex('#',diskspace)-1)
, SUBSTRING(diskspace,1,charindex('*',diskspace)-1) ,
SUBSTRING(diskspace,charindex('*',diskspace)+1,(charindex('#',diskspace)-charindex('*',diskspace))-1)
 
FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2
 
--select * from ##tmp
 
---  End of the routine
 
-- Clean up. See if we need to change the settings back.
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end