Content Database size report using PowerShell – SharePoint 2010


What does it do?
The script will create a report of all Web Applications and the associated Content Databases.
It will tell you how big each Content Database is in GB. The result will be saved in a .txt file. Optionally, you can send this .txt as an attachment in an e-mail.

If you are using this script to mail the report, you might want to add a body to the e-mail, so you can make it look better, but this is not included with the current script.

How to use it
I designed the script so it is easy to schedule as a task. Before you go ahead and schedule it in the task scheduler, you have to add values to some optional and required parameters, For instance, if you want to e-mail this .txt file, you should add at least a SMTP Server, subject, the mail from address and the send to address.

The script

#Get SharePoint Content database sizes
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$date = Get-Date -Format "dd-MM-yyyy"

#Variables that you can change to fit your environment
$TXTFile = "D:\Reports\SPContentDatabase_$date.txt"
$SMTPServer = "yourmailserver"
$emailFrom = "SharePointReports@company.com"
$emailTo = "youradmin@company.com"
$subject = "Content Database size reports"
$emailBody = "Daily/Weekly/Monthly report on Content databases"

$webapps = Get-SPWebApplication
foreach($webapp in $webapps)
{
    $ContentDatabases = $webapp.ContentDatabases
    Add-Content -Path $TXTFile -Value "Content databases for $($webapp.url)"
    foreach($ContentDatabase in $ContentDatabases)
    {
    $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
    Add-Content -Path $TXTFile -Value "-     $($ContentDatabase.Name): $($ContentDatabaseSize)GB"
    }
}
if(!($SMTPServer) -OR !($emailFrom) -OR !($emailTo))
{
Write-Host "No e-mail being sent, if you do want to send an e-mail, please enter the values for the following variables: $SMTPServer, $emailFrom and $emailTo."
}
else
{
Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody -Attachment $TXTFile
}

The result

image

Scheduling the script
Save the script (In my example: C:\Install\Get-SPContentDatabaseSize.ps1)
Start the Task scheduler: Start –> Run: taskschd.msc
Create a new task:

image

General
image

– Make sure the account running the task has sufficient permissions to access the databases
– “Run whether user is logged on or not” should be checked.
– Run with highest privileges has to be checked.

Triggers
image

The triggers can be set to your requirements, in this example I scheduled the task weekly at 6PM.

Actions
To run a PowerShell script as a scheduled task, you cannot simply add the C:\Install\Get-SPContentDatabaseSize.ps1 in the “Program/script” field.
You should do the following:
image
Program/script: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
Add arguments (optional): -Command C:\Install\Get-SPContentDatabaseSize.ps1

Click OK and enter the password for the account that is running the scheduled task, and VOILA.

Advertisements

15 thoughts on “Content Database size report using PowerShell – SharePoint 2010

  1. It not work for my give that error

    Add-Content : No se puede encontrar una parte de la ruta de acceso ‘C:\SPConten
    tDatabase_025\2012.txt’.
    En C:\SPsiteBackup\Get-SPContentDatabaseSize.ps1: 17 Carácter: 16
    + Add-Content <<<< -Path $TXTFile -Value "Content databases for $($webapp.
    url)"
    + CategoryInfo : ObjectNotFound: (C:\SPContentDatabase_025\2012
    .txt:String) [Add-Content], DirectoryNotFoundException
    + FullyQualifiedErrorId : GetContentWriterDirectoryNotFoundError,Microsoft
    .PowerShell.Commands.AddContentCommand

  2. Nice work Nico. I think I’m having the same issues as Gerard. $TXTFile = “D:\Reports\SPContentDatabase_$date.txt” …is “SPContentDatabase_” a directory or part of the concatenation to the date?

    This is what I get
    Add-Content : Could not find a part of the path ‘C:\Reports\SPContentDatabase_0
    5\5\2\2012.txt’.

    I have the output location as “C:\Reports\SPContentDatabase_05”

  3. You’re welcome and Nice Work. Its awesome, even if you just leave the drop directory as $TXTFile = “C:\Reports\SPContentDatabase_$date.txt”, it’d output it in c:\reports.

    Good job man!

  4. I get this error when finalizing this scheduling though “an error has occurred for task GetSPContentDBSizeREport . Error message the following error was reported: the task xml contains value which is incorrectly formatted or out of range.”
    I’m guessing it could a system architecture (x64and x86)issue, but I’m not sure. Care to shed some light…thx

  5. but if i run the scheduler as a basic task, i put the values in and before i save, i add the password and it takes that time.

  6. Nice script Nico! Got it to work.
    Do you know how to amend the script so it shows for each site collection for every web application the ‘Lock status’ i.e. whether it is Locked or Read-Only, No access or adding content prevented.

    • Hi Cliff,

      Thanks for your input. This made me think of a way to do this. Here is the script.
      I will place it in a separate blog too, so people can find it more easily.


      Add-PSSnapin microsoft.sharepoint.powershell -EA silentlycontinue

      $webapps = Get-SPWebApplication
      foreach($webapp in $webapps)
      {
      Write-Host - $webapp.url -Fore Green
      $sites = $webapp.sites
      foreach($site in $sites)
      {
      if($site.readlocked -eq $False -AND $site.WriteLocked -eq $False -AND $site.ReadLocked -eq $False){Write-Host $($site.url): "Not locked"}
      if($site.readlocked -eq $False -AND $site.WriteLocked -eq $True -AND $site.ReadLocked -eq $False){Write-Host $site.url: "Adding content prevented: $($site.LockIssue)"}
      if($site.readlocked -eq $False -AND $site.WriteLocked -eq $True -AND $site.ReadLocked -eq $True){Write-Host $site.url: "Not locked: $($site.LockIssue)"}
      if($site.readlocked -eq $null -AND $site.WriteLocked -eq $null -AND $site.ReadLocked -eq $null){Write-Host $site.url: "No access"}
      }
      Write-host ""
      }

      • Hi Nico, I struggle with PowerShell. Could you help and amend the script above so it saves to a text file with date of file as part of the name and sends an email. That would be very helpful and appreciated.

  7. Hi Nico, wonderful works! – However, I would like to have the script like the previous where it saves to a txt file and sends an email. This would make it more useful when I come first thing in the morning and see the email. Thanks for your great post.

  8. Hi I dont want to send it in email, i just want to save result in the txt file, i have tried below script please verify.

    Web Application URL: http://test-khare.dev.qintra.com/

    Please put this web app url and make this script as i am new to powershell, If it is required to put the name of Content database in the script please mention _WSS_Content_DB, i will put cdb there.
    =============================================================

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    $date = Get-Date -Format “dd-MM-yyyy”

    $TXTFile = “D:\SPContentDatabase_$date.txt”

    $webapps = Get-SPWebApplication
    foreach($webapp in $webapps)
    {
    $ContentDatabases = $webapp.ContentDatabases
    Add-Content -Path $TXTFile -Value “Content databases for $($webapp.http://test-khare.dev.qintra.com/)”
    foreach($ContentDatabase in $ContentDatabases)
    {
    $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
    Add-Content -Path $TXTFile -Value “- $($ContentDatabase.Name): $($ContentDatabaseSize)GB”
    }
    }

  9. Pingback: Content Database FAQ – SharePoint 2010 – .NET

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s