Get SQL database size using Windows Powershell

The following script examines servers from (part of) your Active Directory domain, identifies SQL servers and lists the instances with their total database sizes.

# Get-SQLSizes.ps1
# Created by Hugo Peeters
# http://www.peetersonline.nl
# Description: Finds SQL Servers in Active Directory and gets the total database size in GB.
 
#Region VARIABLES
	$SearchRoot = "OU=Servers,DC=domain,DC=local"
	$OutFile = 'D:\scripts\SQLSizes.csv'
	$Digits = 2
#EndRegion VARIABLES
 
#Region SCRIPT
	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
	$myCol = @()
	ForEach ($Server in (Get-QADComputer -SearchRoot $SearchRoot -SizeLimit 0 | Sort Name))
		{
		ForEach ($Instance in (Get-WmiObject -Class Win32_Service -ComputerName $Server.Name | Where {$_.Name -like 'MSSQL$*'}))
			{
			If ($Instance -eq $null){break}
			# Connect to SQL
			$InstanceString = "{0}\{1}" -f $Server.Name, $Instance.Name.Split('$')[1]
			$Sql = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceString
			# Gather information
			$myObj = "" | Select Instance, TotalDBSizeGB
			$myObj.Instance = $InstanceString
			$myObj.TotalDBSizeGB = [Math]::Round((($Sql.Databases | ForEach {$_.Size} | Measure-Object -Sum).Sum * 1MB / 1GB),$Digits)
			$myCol += $myObj
			# Cleanup
			Clear-Variable Sql -ErrorAction SilentlyContinue
			Clear-Variable InstanceString -ErrorAction SilentlyContinue
			}
		}	
#EndRegion SCRIPT
 
#Region OUTPUT
	$myCol | Export-Csv $OutFile -NoTypeInformation
	Invoke-Item $OutFile
#EndRegion OUTPUT

4 thoughts on “Get SQL database size using Windows Powershell”

  1. Nice script Hugo.
    If you replace the inner loop by this:

    foreach ($Instance in (Get-WmiObject -class Win32_Service -computerName $Server.Name -filter “name like ‘mssql%’”))

    the script will run considerably faster (at least in my environment ;-) )

    1. Thanks for the improvement Luc!
      I have never been a big star in WQL (have to learn yet another syntax), but I understand how that will make it quicker.
      Hugo

  2. I’m a novice at powershell and can’t figure out how to run this. I worked out that it has a dependency on a library from Quest, which I installed and loaded with ‘add_PSSnapin’, and after editing the directory path the script got off to a good start. However it crashed out with an error ‘Get-QADComputer: a referral was returned from the server’. I guessed permissions might be involved so logged on as a domain administrator, and ran ‘powershell’ with ‘run as admin’ but that didn’t help. I’m trying it under Windows 7, which might be related!

    Any advice would be apppreciated

    »crosslinked«

Leave a Reply