Here is the code I use to get mount point information. I'm sure someone has a better way but I couldn't find it
/*
This code will go out to the O/S and check for free space at the drive level
It will only check volumes that have database files (mdf,ldf,ndf) (sysaltfiles)
It will only check volumes attached to the node
*/
Declare @svrName varchar(255)
, @sql varchar(400)
, @Path varchar(400)
, @Label varchar(400)
, @Capacity Decimal(12,2)
, @FreeSpace Decimal(12,2)
, @PercentFree Decimal(12,2)
, @Row int
, @MessageBody NVARCHAR(MAX)
, @Subject NVARCHAR(250)
--
Declare @output TABLE
( row int IDENTITY(1,1) NOT NULL
, line varchar(255) )
--
SET NOCOUNT ON
--
BEGIN -- use powershell to go ou to the O/S and get a list of all volumes attached to the server
set @sql = 'powershell.exe -c "gwmi win32_volume'
+ '|'
+ 'where-object {$_.filesystem -match ''ntfs''}'
+ '|'
+ 'format-list name,capacity,freespace,label"'
--
insert @output
EXEC xp_cmdshell @sql
--
Delete @output where line is null
END
--
WHILE EXISTS ( SELECT *
FROM @OutPut
where line like 'name : %'
or line like 'capacity : %'
or line like 'label : %'
OR line like 'freespace : %' )
BEGIN
SELECT TOP 1
@Row = row
, @Path = substring(Line,13,len(Line))
FROM @Output
WHERE Line like 'name : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @Label = substring(Line,13,len(Line))
FROM @Output
WHERE Line like 'label : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @Capacity = cast(substring(Line,13,len(line)) as bigint)/1048576.00
FROM @Output
WHERE Line like 'capacity : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @FreeSpace = cast(substring(line,13,len(line)) as bigint)/1048576.00
FROM @Output
WHERE Line like 'freespace : %'
DELETE @Output
WHERE row = @Row
--
SET @PercentFree = cast( ( ( ( @Capacity - ( @Capacity - @FreeSpace ) ) / @Capacity ) * 100.00 ) as decimal(12,2) )
--
IF ( @PercentFree < 6
and @Path not like '\\?\Volume%'
and @Path in ( select reverse(substring(reverse(filename),charindex('\',reverse(filename)),100)) from master.dbo.sysaltfiles) )
BEGIN -- Ready email body
Select @MessageBody = 'Disk Space Alert'
+ char(10) + char(13)
+ ' Computer Name = ' + cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar(25))
+ char(10) + char(13)
+ ' Instance Name = ' + cast(@@servername as nvarchar(50))
+ char(10) + char(13)
+ ' Drive or Path = ' + @Path
+ char(10) + char(13)
+ ' Label = ' + @Label
+ char(10) + char(13)
+ ' Capacity (MB) = ' + cast(@Capacity as nvarchar(20))
+ char(10) + char(13)
+ ' Free Space (MB) = ' + cast(@FreeSpace as nvarchar(20))
+ char(10) + char(13)
+ ' %Free Space = ' + cast(@PercentFree as nvarchar(20))
+ char(10) + char(13)
+ ' EventTime = ' + convert(varchar, getdate())
+ char(10) + char(13)
--
BEGIN -- Send Email
SET @SUBJECT = 'SQL Monitor Disk Space Alert: '+ @Path + ' is at ' + cast(@PercentFree as nvarchar(20)) + '% free'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLDBA Profile'
, @recipients = 'My.Email@MyCompany.com'
, @SUBJECT = @Subject
, @Body = @MessageBody
, @importance = 'High'
, @exclude_query_output = 1
END
--
END
END