PoshUtils: Get-DBSize – Get SQL Database space usage

Here is an improved version of the script I am using to get the database space usage details. This is one of the utility scripts I have been using and will be publishing soon as PoshUtils on Codeplex.

For this script to work, you need the SQL Management Object (SMO) assemblies. These assemblies are available on systems with SQL server installed or where SQL management tools are installed.

Function Get-DBSize {
 [CmdletBinding()]
 param (
  [Parameter(Mandatory=$false)]
     [String[]]
        $dbname,            

     [Parameter(Mandatory=$false,
            ValueFromPipeline=$true)]
        [alias("cn","server","computer")]
  [String[]]
        $computername = "localhost"            

 )            

    Process {
        Foreach($computer in $computerName) {
            Write-Verbose "Attempting to load SQL SMO.."
            try {
                add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
            }
            catch {
                Write-Error $_
                return
            }                            

            Write-Verbose "Attempting to connect to the SQL instance on: ${computer}"
            try {
                $server = New-Object Microsoft.SqlServer.Management.Smo.Server($computer)
            }
            catch {
                Write-Error $_
            }
            if ($dbname) {
                foreach ($db in $dbname) {
                    Write-Verbose "Retrieving space usage details for ${db}"
                    if ($server.Databases[$db]) {
                        $server.Databases[$db] | Select Name,DataSpaceUsage,IndexSpaceUsage,SpaceAvailable,Size
                    } else {
                        Write-error "Database ${db} does not exist on ${ComputerName}"
                    }
                }
            } else {
                foreach ($db in $server.databases) {
                    Write-Verbose "Retrieving space usage details for ${db}"
                    $db | Select Name,DataSpaceUsage,IndexSpaceUsage,SpaceAvailable,Size
                }
            }
        }
    }
}

Here are some examples:

Get-DBSize -ComputerName Server01

This gets the database space usage details for all databases on a SQL server named Server01.

Get-DBSize -DBName TestDB1,TestDB2 -ComputerName Server02

The above example gets the space usage details for TestDB1 and TestDB2 on  a SQL Server named Server02.

Get-DBSize -DbName TestDB1,TestDB2

This last example get the space usage details for TestDB1 and TestDB2 on the local SQL server. And, finally, you can avoid all parameters to get space usage for all databases on the local SQL Server.

Here is how the output looks:

Get-DBSize

Ravikanth

A technology enthu and a Windows PowerShell MVP working on SharePoint solutions at Dell Inc. Has deep interests in Windows Server OS & Virtualization.

More Posts - Website - Twitter - Facebook