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{

Scripts out database size properties

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 

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

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

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


		$objects = @();
		$srvConn = New-Object microsoft.SqlServer.Management.Common.ServerConnection
		$srvConn.LoginSecure = $true;
		$srvConn.ServerInstance = $serverInstance
		$server = New-Object microsoft.SqlServer.Management.Smo.Server $srvConn
		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 

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 )

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: