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
# Description: Finds SQL Servers in Active Directory and gets the total database size in GB.
	$SearchRoot = "OU=Servers,DC=domain,DC=local"
	$OutFile = 'D:\scripts\SQLSizes.csv'
	$Digits = 2
#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

Leave a Reply

Close Menu