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.

	[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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: