Get database file size in powershell

This script will return database size, database usage, index space usage, the space of the log used and the log size.  It assumes windows auth.  Marginally tested, use at your own risk.

function Get-DatabaseSize{
<#

.SYNOPSIS
Scripts out database size properties

.DESCRIPTION
This module will script sql server database sizes for all databases on a given server instance

.PARAMETER serverInstance
The name of the sql server instance that the jobs reside on

.PARAMETER IncludeSystemDBs
Switch to include system databases in results 

.EXAMPLE
Get database sizes for all databases including system databases
Get-DatabaseSize -serverInstance 'InstanceName' -IncludeSystemDBs

.EXAMPLE 
Get database sizes for all databases via pipeline
@('instance1', 'instance2', 'instance3') | Get-DatabaseSize -IncludeSystemDBs

.NOTES
This assumes that you have access to the sql server via windows-authentication. 

#>

	param(
		[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
		[string]$serverInstance,
		[Parameter(Mandatory=$false,Position=1,ValueFromPipeline=$false)]
		[switch]$IncludeSystemDBs
	)
	begin{
		$objects = @();
		$srvConn = New-Object microsoft.SqlServer.Management.Common.ServerConnection
		$srvConn.LoginSecure = $true;
		$srvConn.ServerInstance = $serverInstance
		$server = New-Object microsoft.SqlServer.Management.Smo.Server $srvConn
	}
	process{
		foreach($db in $server.Databases | where{$_.IsSystemObject -eq $IncludeSystemDBs -or -not $_.IsSystemObject}){
			$logSize = 0;
			$UsedLogSpace = 0;
			foreach($log in $db.LogFiles){
				$logSize += $log.Size
				$UsedLogSpace += $log.UsedSpace
			}
			$obj = New-Object -TypeName PSObject -Property @{
				ServerInstance = $serverInstance
				DatabaseName = $db.Name
				DatabaseSizeMB = $db.Size
				DataSpaceUsageKB = $db.DataSpaceUsage
				IndexSpaceUsageKB = $db.IndexSpaceUsage
				UsedLogSpaceKB = $UsedLogSpace
				LogSizeKB = $logSize
			}
			$objects += $obj;
		}
		$objects | SELECT ServerInstance, DatabaseName, DatabaseSizeMB, DataSpaceUsageKB, IndexSpaceUsageKB, UsedLogSpaceKB, LogSizeKB 
	}
	end{
		$srvConn.Disconnect();
	}
}
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

%d bloggers like this: