Quick script (I think I start off all my posts with those two words) on how to get the free space of your transaction log file (along with other information about the files) with powershell. Useful if you manually grow your log files (like we all should do, but most probably don’t [myself included]). As always, this uses SQLPSX. One of these days I’ll convert to SQLPS.
param(
[string[]]$servers = @('ServerName')
)
Import-Module SqlServer
$outputs = @();
$servers | %{
$serverName = $_
Get-SqlDatabase -sqlserver $(Get-SqlServer -sqlserver $_ -username 'username' -password 'password') | %{
$db = $_
Get-SqlLogFile -database $_ | %{
$output = New-Object -TypeName PSObject -Property @{
ServerName = $serverName
DatabaseName = $db.Name
FileLogicalName = $_.Name
FileLocation = $_.FileName
FileGrowthKB = $_.Growth
MaxSizeKB = $_.MaxSize
AvailableSpace = ($_.Size - $_.UsedSpace)
UsedSpace = $_.UsedSpace
Size = $_.Size
PercentFreeSpace = [Math]::Truncate((($_.Size - $_.UsedSpace) / $_.Size) * 100)
}
$outputs += $output
}
}
}
$outputs | SELECT ServerName, DatabaseName, FileLogicalName, FileLocation, PercentFreeSpace, UsedSpace, AvailableSpace, Size | sort PercentFreeSpace -Descending | Format-Table -AutoSize
Sql Server Powershell 
Leave a comment