List db size on remote server with powershell


Today, I wanted to create a powershell script to list all databases on a remote SQL Server and their total size in MB. I haven’t made the script as robust as needed, but this should be a nice first version. The script will connect to the server given in parameter $server. I succesfully connected, the script will calculate total size of all databases, and print a header, before it lists all database with it’s size in MB.

Param ($Server = "localhost")

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') `
		-argumentlist $Server

$dataTotal = $SMOserver.Databases | Measure-Object -property Size -sum

write-host "********************************************"
Write-host "Connected to server '$server'"
write-host "  DB Count    = " $dataTotal.count
write-host "  Total size  = " $dataTotal.sum.ToString("#,##0") "MB"
write-host "********************************************"
$x2 = @{label="Size (MB)";Expression={$_.Size};FormatString="#,###,##0"}

$SMOserver.Databases `
	| select name, size `
	| sort-object Size -descending `
	| Format-Table Name, $x2 -auto
Advertisements

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 )

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