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
Tagged with:
 

4 Responses to Get SQL database size using Windows Powershell

  1. LucD says:

    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 ;-) )

    • admin says:

      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. Chris says:

    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