Tag Archives: Size

Use PowerShell to get all your SQL databases and their size

Posted on by
Reply

Quick script to get all the databases on a server, then output to a CSV file.

In this example, all I needed was the database name and its size.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YourSQLServer"
$dbs=$s.Databases
$dbs | SELECT Name, Size | Export-Csv c:\test.txt

Other properties:

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

Credit for the base script:
Edwin M Sarmiento

Monitor Content Database Size with Powershell

Posted on by
2

Simple question.
How large are the content databases?

To solve this, I created a list in Central Administration, and wrote a quick PowerShell script to update the list with my Content Database size(s).

List Name: Content db Size
List Columns: Title, db name, db size (Type = Single line of text)
View: Show Title, db name, db size

PowerShell Script:

if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
$adminwebapp = Get-spwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}

$siteUrl = $adminwebapp.Url
$listName = "Content db Size"
$spSite = new-object Microsoft.SharePoint.SPSite($siteurl)
$spWeb = $spSite.OpenWeb()
$spList = $spWeb.Lists[$listName]
foreach ($item in $spList.items) { $deaditem = $splist.GetItemById($item.ID); $deaditem.Delete(); }

$webapps = Get-SPWebApplication
foreach($webapp in $webapps)
{
    $ContentDatabases = $webapp.ContentDatabases
    foreach($ContentDatabase in $ContentDatabases)
    {
    $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
	$addItem = $spList.Items.Add()
	$addItem["Title"] = $webapp.DisplayName
	$addItem["db name"] = $contentdatabase.name
	$addItem["db size"] = $contentdatabasesize
	$addItem.Update()
    }
}

$spWeb.Dispose()

The script is doing the following actions:
Truncate the Content db Size list.
Looping through all the Web Apps.
Updating the Content db Size list with the Web App name, db name, and size.

Taking this to the next level:
Update the existing list item, opposed to truncating the entire list.
Setup a workflow to email you if a db size is greater than X.

*update*

What if you want to get all the Site Collections in a Content Database?
 

$sites = Get-SPSite -Limit All  -ContentDatabase YourContentDatabaseName

foreach($site in $sites)
{
	$siteSize = [math]::round(($site.usage.storage/1GB),2)
	Write-Host $site.url "--" $siteSize
}

Get The Size of a Document Library

Posted on by
Reply

“How large is my Document Library”

This was an odd one to crack. For reporting reasons, our team wanted to track the item count and size growth of a Document Library. Sound easy?

if(-not(Get-PSSnapin | where { $_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
      Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$SPsite = Get-SPsite "http://www.sharepointed.com/sites/taco/"

$dataTable = $SPsite.StorageManagementInformation(2,0x11,0,0)
foreach($x in $dataTable.Rows)
{
	if ($x.LeafName -eq "MyTacoLibrary" -and $x.Directory -eq "sites/taco")
		{
			$LibSize = [Math]::Round(($x.Size/1GB),2)
			Write-Host $LibSize
		}
}

Thanks you Jon for guiding this script.