February 22, 2012 at 1:09 am
Comments posted to this topic are about the item Find storage including mount points sql/powershell
June 13, 2012 at 10:06 pm
It was really helpful, Gene. Thanks
September 5, 2014 at 3:56 am
Actually, if you just want to see the volumes/mount points that are currently in use by this sql instance, you don't need powershell.
I use this query across all my sql instances using powershell to collect and centralise the data. Is very useful for capacity planning.
;WITH FileIds
AS
( SELECT DISTINCT database_id, file_id
FROM MASTER.SYS.master_files AS mf
)
SELECT DISTINCT
volume_mount_point ,
logical_volume_name,
(total_bytes / 1073741824) SizeGB ,
(available_bytes / 1073741824) FreeSpaceGB,
(available_bytes / 1073741824.0)/ (total_bytes / 1073741824.0)*100 PercentFree
FROM FileIds f
CROSS APPLY sys.dm_os_volume_stats (f.database_id, f.file_id)
July 20, 2015 at 9:41 pm
The powershell script below will return information on drives and mountpoints regardless of whether they host database files.
It has some minor deficiencies but works OK.
Has a critical threshold [%], below which items are highlighted with a red background.
Set the value of variable $server
param(
[int]$MountPointsOnly = $false,
[int]$CriticalThreshold = 15
)
$ErrorActionPreference = "Stop"
Clear-Host
$server = 'ThisServer'
$serverExists = ping "$server" -ne 1
if($serverExists -like "*Ping request could not find host*"){
Write-host "***`n`tCould not connect to server: $server`n***" #-BackgroundColor White -ForegroundColor Red
}else{
try{
if($MountPointsOnly){
$v = gwmi -class "win32_volume" -namespace "root/cimv2" -ComputerName $server | Where-Object {$_.capacity -ne $null -and $_.DriveLetter -eq $null}
}else{
$v = gwmi -class "win32_volume" -namespace "root/cimv2" -ComputerName $server | Where-Object {$_.capacity -ne $null}
}
$OddLine = $true
$PadChar = '.'
if($v -eq $null){
Write-Host "No Mountpoints present on server: $server" `
}else{
Write-Host "Storage Space Report [in GB] - $server"
Write-Host "Drive Label".padright(40," ") -BackgroundColor DarkBlue `
"Free".padleft(5," ") `
"Total".padleft(7," ") `
"% Free ".padleft(12," ") `
-ForegroundColor White
foreach($d in $v | sort -Property caption){
if($OddLine){
$PadChar = ' '
$background = "Gray"
$OddLine = $false
} else {
$PadChar = ' '
$background = "White"
$OddLine = $true
}
if(($d.freespace /$d.Capacity*100) -lt $CriticalThreshold){
write-host (($d.DriveLetter + "").padright(4," ") + " " + $d.label).padright(40,$PadChar) ("{0:#,##0}" -f ($d.freespace/ 1GB)).padleft(5," ") " " `
("{0:#,##0}" -f ($d.capacity/ 1GB)).padleft(5," ") " " `
('{0:P2}' -f ($d.freespace /$d.Capacity)).padleft(10," ") -BackgroundColor Red -ForegroundColor Black
}else{
write-host (($d.DriveLetter + "").padright(4," ") + " " + $d.label).padright(40,$PadChar) ("{0:#,##0}" -f ($d.freespace/ 1GB)).padleft(5," ") " " `
("{0:#,##0}" -f ($d.capacity/ 1GB)).padleft(5," ") " " `
('{0:P2}' -f ($d.freespace /$d.Capacity)).padleft(10," ") -BackgroundColor $background -ForegroundColor Black
}
}
}
}catch{
Write-host "*** Could not connect to server: $server ***" -BackgroundColor White -ForegroundColor Red
}
}
May 11, 2016 at 7:11 am
Thanks for the script.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply