Summary: This post describes how to get a content database size report using PowerShell.
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:ReportsSPContentDatabase_$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
Scheduling the script
Save the script (In my example: C:InstallGet-SPContentDatabaseSize.ps1)
Start the Task scheduler: Start –> Run: taskschd.msc
Create a new task:
General
– 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
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:InstallGet-SPContentDatabaseSize.ps1 in the “Program/script” field.
You should do the following:
Program/script: %SystemRoot%system32WindowsPowerShellv1.0powershell.exe
Add arguments (optional): -Command C:InstallGet-SPContentDatabaseSize.ps1
Click OK and enter the password for the account that is running the scheduled task, and VOILA.
It not work for my give that error
Add-Content : No se puede encontrar una parte de la ruta de acceso ‘C:SPConten
tDatabase_0252012.txt’.
En C:SPsiteBackupGet-SPContentDatabaseSize.ps1: 17 Carácter: 16
+ Add-Content <<<< -Path $TXTFile -Value "Content databases for $($webapp.
url)"
+ CategoryInfo : ObjectNotFound: (C:SPContentDatabase_0252012
.txt:String) [Add-Content], DirectoryNotFoundException
+ FullyQualifiedErrorId : GetContentWriterDirectoryNotFoundError,Microsoft
.PowerShell.Commands.AddContentCommand
Nice work Nico. I think I’m having the same issues as Gerard. $TXTFile = “D:ReportsSPContentDatabase_$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:ReportsSPContentDatabase_0
5522012.txt’.
I have the output location as “C:ReportsSPContentDatabase_05”
Hi Shile,
Yes you are right. I have updated the script so it will always use the same format, regardless of the server settings.
Thanks for the feedback!
You’re welcome and Nice Work. Its awesome, even if you just leave the drop directory as $TXTFile = “C:ReportsSPContentDatabase_$date.txt”, it’d output it in c:reports.
Good job man!
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
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.
I must say, I have never come across this specific error for the Task Scheduler. With the basic task, does it work as expected?
running as expected, thanks very much.
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.
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.
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”
}
}
We have created nice script to extract this report for multiple farms, it also reports as orange when DB size is 80 GB, red in case database size is over 100 GB. It also report as orange if number site collection is on warning level.
http://msexchange.me/2014/08/10/sharepoint-content-database-size-report/