Day: April 24, 2013

Get Sql Server Log File Free Space via Powershell

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