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

This script builds up on http://www.cookingsql.com/2014/12/free-space-on-drives/ and correlate the data with the location of data files and their sized.

/*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 
Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers

 
*/
declare @t table 
([database_name] sysname, LogicalDBName sysname, type_desc nvarchar(10), sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
type_desc,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

select * from @t

This script joins two scripts

•  http://www.cookingsql.com/2014/12/free-space-on-drives/
•  http://www.cookingsql.com/2014/12/data-file-sizes-and-free-space/

We first get the free space o drives, and correlate the data (inner join) with the location of data files

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher

Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

 /*
DESCRIPTION    List space on database files for each database and its location
 
CONFIGURATION    none
 
Compatibility list:
MSSQL2008R2 and higher



Issues: Some time it has issues when the database name is too long, such as in some "Sharepoint" servers
 
*/

/* This section checks if xp_cmdshell is enabled, and keeps track of the settings */ 
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 will 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 'Ole Automation Procedures',1
 RECONFIGURE WITH OVERRIDE
 set @op_ch2 = 1
end
 
/*
This section queries space avail on each drive.
*/
-- ==============
 
DECLARE @cmd2 NVARCHAR(2000)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
 
DECLARE @HOSTNAME varchar(20)
 
-- checks if this is a cluster
select top 1  @HOSTNAME=nodename from ::fn_virtualservernodes()
IF @HOSTNAME is NULL set @hostname = cast(ServerProperty('machinename') as nvarchar(10))
 
declare @temp table 
(servername nvarchar(100), database_name nvarchar(100),driveletter nvarchar(2),totaldriveMB int,
 freedriveMB int,FreeDiskPct nvarchar(3), filename nvarchar(100), filesizeMB nvarchar(10), freeSpaceMB int,
FreeSpacePct varchar(8),AutoGrowth nvarchar(100), MaxsizeMB int, phys nvarchar(100))
 
declare @drives TABLE
(ServerName varchar(15),  drive char(1) PRIMARY KEY,
 FreeSpace int NULL,  TotalSize int NULL)  
 
INSERT @drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
DECLARE dcur CURSOR LOCAL FAST_FORWARD
 
FOR SELECT drive from @drives
ORDER by drive
OPEN dcur
 
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
 
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE @drives
SET TotalSize=@TotalSize/@MB, ServerName = @HOSTNAME
WHERE drive=@drive
 
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
 
EXEC @hr=sp_OADestroy @fso
 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
 
 
 /* This section turns off the xp_cmdshell settings if needed  */
 
if @op_ch2 = 1
begin
 EXEC sp_configure 'Ole Automation Procedures', 0
 RECONFIGURE WITH OVERRIDE;
end
 
if @op_ch1 = 1
begin
 EXEC sp_configure 'show advanced options',0
 RECONFIGURE WITH OVERRIDE;
 
end



/* This section queries every database for space stats.*/  

declare @t table 
([database_name] sysname, LogicalDBName sysname, sizeMB bigint, FreeSpaceMB bigint,
freespace nvarchar(4), Autogrow nvarchar(100), MAxSize bigint, physname nvarchar(max))

declare @cmd nvarchar (2000)
set @cmd =
'use [?]
SELECT 
''?'',
b.name,
CAST(b.size/128.0 AS int) ''SizeMB'', 
CAST(b.size/128.0 - CAST(FILEPROPERTY(b.name, ''SpaceUsed'' ) AS int)/128.0 AS int) ''FreeSpaceMB'', 
CAST(ceiling(100 * (b.size/128.0 -CAST(FILEPROPERTY(b.name,''SpaceUsed'' ) AS int)/128.0)/(b.size/128.0)) AS varchar(8)) + ''%''  ''FreeSpace'',
    CASE 
        WHEN b.is_percent_growth = 0 
            THEN LTRIM(STR(b.growth * 8.0 / 1024,10,1)) + '' MB, '' 
        ELSE
            ''By '' + CAST(b.growth AS VARCHAR) + '' percent, ''
    END + 
    CASE 
        WHEN b.max_size = -1 THEN ''unrestricted growth''
        ELSE ''restricted growth to '' + 
            LTRIM(STR(b.max_size * 8.0 / 1024,10,1)) + '' MB'' 
    END AS Autogrow,
cast(b.max_size/128.0 as int) ''MaxSize'',
physical_name

FROM [?].sys.database_files b'
insert into @t
Exec sp_MSForEachDB @cmd

-----------------------------------


select * 
from @t t
inner join @drives d on left(t.physname,1)=d.drive