Skip to content

April 24, 2013

Get Sql Server Log File Free Space via Powershell

by Scott Newman

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
Read more from Powershell, SQL Server

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments

%d bloggers like this: