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 Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.