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
No related posts.
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 ;-) )
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
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«
[...] Get SQL database size using Windows Powershell [...]