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
colin kaepernick didn’t vote in the electionLegislation denied: Australia’s government has for the second time rejected proposed legislation cheap football jerseys to create a Wholesale NFL Jerseys carbon trading scheme. I’ll update this article when the results from that clash are announced. There are so many cards on the auction, that your card will not show up until there is about Cheap NFL Jerseys China 4 hours left, once again unless you are listing a very rare card. The man managed to get his stepdad’s underwear over his head, but the waistband caught around his throat, resulting in a death that was simultaneously horrifying and America’s Funniest Home Videos worthy. Once the quarterback has placed the ball in the pocket, or opening, the running back immediately clamps down on the ball protecting it and holding on to it. You just have to do a search for them.. There are many facets to successful sports managing and coaching. Hi, this is Caleb Kelly again. That remains a factor.. Of those states that did report data, Mississippi was the lowest paying state at $133,420. The Rams have a history of Wholesale NFL Jerseys Online playing well within the division and then suffering letdowns against unfamiliar opponents. However, contrary Cheap NFL Jerseys to popular belief, the menopause is not an indicator that fertilisation is not possible.. He’s gotten in trouble off the field. These are also the traditional fields in the art domain.The Jackson State University offers bachelors degree in the fields of science, technology, engineering, education, liberal arts and business. This choice could ultimately change the format of the series, which is at Major League Baseball’s discretion. The Saints won in snowy Philadelphia last weekend, so who knows?. So Fresno is performing below the average market margin of a Cumulus radio station, so there’s some potential upside there as well, which is not factored into the incremental EBITDA on a pro forma basis.. ”The unfortunate appearance of him staggering around took place in the time between the actual impact and the medical staff reaching him,” Seward said.. All these various options and requirements can be quite confusing at times and therefore it is very useful that dedicated wallpaper applications exist in the Android Market. Rather, I think of aggressiveness as a mindset in which athletes are proactive, assertive, and forceful, for example, driving hard to the hoop http://www.cheap-nfl-nike-jerseys.com in basketball, going for a risky shot in golf or tennis, or setting a fast pace http://www.cheapjerseys-nfl.com in a marathon.. If you’re willing to risk your safe haven, you can “steal” a base.

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