Powershell Function to Get Sizes of All Databases on an Instance

Below is a function that I modified from one in Idera’s SQL Server Powershell Scripts toolset:

#Function to get sizes of all DB's hosted on an instance
Function Get-SQLDBSizes
{
	param (
		[string]$InstaceName = "$(Read-Host 'Please use format ServerName(IPAddress)\InstanceName' [e.g. 127.0.0.1\instance])"
	)

	begin {
		[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
	}
	process {
		try {
			Write-Verbose "Connect to SQL Server using IP address, instance and Windows authentication..."

			$dbs = @()

			Write-Verbose "Creating SMO Server object..."
			$smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $InstaceName

			# Use Windows Authentication by setting LoginSecure to TRUE
			Write-Verbose "Setting Windows Authentication mode..."
			$smoServer.ConnectionContext.set_LoginSecure($True)

			# Output object that contains a of the databases
			foreach ($Database in $smoServer.Databases) {
				$db = New-Object -TypeName PSObject -Property @{
					'DBOwner' = $Database.Owner
					'DBName' = $Database.Name
					'DBSize(MB)' = "{0:N0}" -f $Database.Size
				}
				$dbs += $db
			}
			Write-Output $dbs | Format-Table -AutoSize
		}
		catch [Exception] {
			Write-Error $Error[0]
			$err = $_.Exception
			while ( $err.InnerException ) {
				$err = $err.InnerException
				Write-Output $err.Message
			}
		}
	}
}#End of function
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Powershell and tagged , . Bookmark the permalink.

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