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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy