Quick script to get the free space in your data files in sql server. Not really tested or anything. Use at your own risk.
cls
$server = Get-SqlServer -sqlserver "ServerName"
$outputs = @();
foreach($db in $server.Databases | where{!($_.IsSystemObject)})
{
foreach($fileGroup in $db.FileGroups)
{
foreach($dataFile in $fileGroup.Files)
{
$space = ($dataFile.AvailableSpace * 1024)
$output = New-Object -TypeName PSObject -Property @{
DBName = $db.Name
FileName = $dataFile.FileName
Type = "Data File"
FreeSpaceGB = ($space / 1GB)
}
$outputs += $output;
}
}
foreach($logFile in $db.LogFiles)
{
$space = (($logFile.Size - $logFile.SpaceUsed) * 1024)
$output = New-Object -TypeName PSObject -Property @{
DBName = $db.Name
FileName = $logFile.FileName
Type = "Log File"
FreeSpaceGB = ($space / 1GB)
}
$outputs += $output;
}
}
$outputs | SELECT DBName, FileName, Type, FreeSpaceGB | Format-Table -AutoSize
Leave a comment