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