How to determine the size of the SharePoint Content DBs in a SharePoint Farm!

By Danielle Arad - June 20, 2014

One of the most common administration tasks when managing a SharePoint environment is to have under control the sizes of the SharePoint content databases available in a SharePoint farm. Although there are several possibilities to get this information, I recommend you to use Windows PowerShell so you can take advantage of the features and great capabilities it provides when doing SharePoint administration task. As you will learn in this article, you can easily get the size for your SharePoint Content Databases by means of a PowerShell Script.

 

To build and run the script it is advisable to use ISE (Integrated Script Shell Environment) tool available in Windows Server (versions 2008 to 2012 R2). ISE can be considered as a development environment (IDE) since it allows you to define, test and run PowerShell scripts and which provides capabilities such as support when writing PowerShell code intellisense, debugging, etc.

In order to get the size for all the SharePoint Content DBs in a SharePoint 2013 CloudShare environment:

  • Open ISE in your SharePoint 2013 Web Front End and add the following PowerShell Script:
<span id="lnum1" style="color: #606060;"> 1:</span> #####################################################################################################

2: # Script that gets content databases sizes for all the web applications in a SharePoint Farm

<span id="lnum3" style="color: #606060;"> 3:</span> # <span style="color: #0000ff;">Parameters</span>: N/A<!--CRLF-->
<span id="lnum4" style="color: #606060;"> 4:</span> #####################################################################################################<!--CRLF-->
<span id="lnum5" style="color: #606060;"> 5:</span> <!--CRLF-->
<span id="lnum6" style="color: #606060;"> 6:</span> <span style="color: #0000ff;">If</span> ((<span style="color: #0000ff;">Get</span>-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $<span style="color: #0000ff;">null</span> ) <!--CRLF-->
<span id="lnum7" style="color: #606060;"> 7:</span> { <span style="color: #0000ff;">Add</span>-PSSnapIn -Name Microsoft.SharePoint.PowerShell }<!--CRLF-->
<span id="lnum8" style="color: #606060;"> 8:</span> <!--CRLF-->
<span id="lnum9" style="color: #606060;"> 9:</span> $<span style="color: #0000ff;">host</span>.Runspace.ThreadOptions = "ReuseThread"<!--CRLF-->
<span id="lnum10" style="color: #606060;"> 10:</span> #<span style="color: #0000ff;">Function</span> that gets the <span style="color: #0000ff;">size</span> <span style="color: #0000ff;">of</span> <span style="color: #0000ff;">all</span> the content databases <!--CRLF-->
<span id="lnum11" style="color: #606060;"> 11:</span> <span style="color: #0000ff;">function</span> <span style="color: #0000ff;">Get</span>-ContentDBSizes<!--CRLF-->
<span id="lnum12" style="color: #606060;"> 12:</span> { <!--CRLF-->
<span id="lnum13" style="color: #606060;"> 13:</span> try<!--CRLF-->
<span id="lnum14" style="color: #606060;"> 14:</span> {<!--CRLF-->
<span id="lnum15" style="color: #606060;"> 15:</span> $spWebApps = <span style="color: #0000ff;">Get</span>-SPWebApplication -IncludeCentralAdministration<!--CRLF-->
<span id="lnum16" style="color: #606060;"> 16:</span> foreach($spWebApp <span style="color: #0000ff;">in</span> $spWebApps) <!--CRLF-->
<span id="lnum17" style="color: #606060;"> 17:</span> { <!--CRLF-->
<span id="lnum18" style="color: #606060;"> 18:</span> #$spWebApp.Name<!--CRLF-->
<span id="lnum19" style="color: #606060;"> 19:</span> $ContentDatabases = $spWebApp.ContentDatabases<!--CRLF-->
<span id="lnum20" style="color: #606060;"> 20:</span> foreach($ContentDatabase <span style="color: #0000ff;">in</span> $ContentDatabases)<!--CRLF-->
<span id="lnum21" style="color: #606060;"> 21:</span> { <!--CRLF-->
<span id="lnum22" style="color: #606060;"> 22:</span> $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)<!--CRLF-->
<span id="lnum23" style="color: #606060;"> 23:</span> $ContentDatabaseInfo= $spWebApp.DisplayName + "," + $ContentDatabase.Name + "," + $ContentDatabaseSize + " GB" <!--CRLF-->
<span id="lnum24" style="color: #606060;"> 24:</span> $ContentDatabaseInfo<!--CRLF-->
<span id="lnum25" style="color: #606060;"> 25:</span> #<span style="color: #0000ff;">Write</span>-<span style="color: #0000ff;">Host</span> " * " $spWebApp.DisplayName "-" $ContentDatabase.Name ": " $ContentDatabaseSize " GB"<!--CRLF-->
<span id="lnum26" style="color: #606060;"> 26:</span> } <!--CRLF-->
<span id="lnum27" style="color: #606060;"> 27:</span> } <!--CRLF-->
<span id="lnum28" style="color: #606060;"> 28:</span> }<!--CRLF-->
<span id="lnum29" style="color: #606060;"> 29:</span> catch [System.<span style="color: #0000ff;">Exception</span>]<!--CRLF-->
<span id="lnum30" style="color: #606060;"> 30:</span> {<!--CRLF-->
<span id="lnum31" style="color: #606060;"> 31:</span> <span style="color: #0000ff;">write</span>-<span style="color: #0000ff;">host</span> -f red $_.<span style="color: #0000ff;">Exception</span>.ToString()<!--CRLF-->
<span id="lnum32" style="color: #606060;"> 32:</span> }<!--CRLF-->
<span id="lnum33" style="color: #606060;"> 33:</span> } <!--CRLF-->
<span id="lnum35" style="color: #606060;"> 34:</span> <span style="color: #0000ff;">Start</span>-SPAssignment –<span style="color: #0000ff;">Global</span><!--CRLF-->
<span id="lnum36" style="color: #606060;"> 35:</span> <span style="color: #0000ff;">Get</span>-ContentDBSizes &gt; ContentDBs.csv<!--CRLF-->
<span id="lnum37" style="color: #606060;"> 36:</span> Stop-SPAssignment –<span style="color: #0000ff;">Global</span><!--CRLF-->
<span id="lnum38" style="color: #606060;"> 37:</span> Remove-PsSnapin Microsoft.SharePoint.PowerShell
  • Save the script in ISE, and execute it so that you get the results ContentDBs.csv file. This file stores the size for each content database in your CloudShareEnvironment.

CloudShare_1

  • In the particular case of my SharePoint 2013 CloudShare, the result obtained one the script is executed is the following one:

CloudShare_2

You can download this script from the Microsoft TechNet Script Gallery where I use to contribute with some useful PowerShell Scripts. And that’s all about how to get the size for all the SharePoint Content Databases using PowerShell. Happy CloudSharing!