Content Database FAQ – SharePoint 2010

Last update: 09-11-2012
Since there is a lot of misconception on how different SharePoint components relate to each other, I will try to answer some common questions. Please feel free to comment or ask additional questions in the comment section. I will add them to the FAQ list.

The below figure shows the infrastructure of Content databases related to Web Applications and site collections.

SharePoint_Infrastructure

FAQ

1. Can we create a site collection in a specific Content Database?

Yes, but you can only do this by using PowerShell using the New-SPSite cmdlet. You can use the "-ContentDatabase" parameter to specify in which Content Database the site collection will be created.

2. When using the "-ContentDatabase" parameter in the New-SPSite cmdlet, does the content database get automatically created if it does not exist?
No, if you enter a Content Database name that does not exist, you will get an error saying the content database could not be found.

3. My site collection is getting very big, can I add a second Content Database to spread the load?
Because of the way SharePoint is designed, you cannot have 1 site collection spread over multiple Content Databases. If you want to split up your site collection in to multiple site collections, you can do this by exporting and importing subsites, or by using 3rd party tools.

4. What site collections are currently in my Content Database?
For this specific question, I have written a PowerShell one-liner that will return every Content Database with their respective site collections. You can find this one liner here: http://sharepointrelated.com/2012/03/09/get-site-collections-per-content-database-one-liner/

5. How can I check the size of my Content Database?
The easiest way to accomplish this is by looking at the database file on your SQL Server. But there may be some scenario’s where this is not possible. Insufficient permissions being the most common one. In this case, you can use this article to find out the approximate Content Database size: http://sharepointrelated.com/2012/03/07/content-database-size-report-using-powershell/

6. What Content Databases are attached to my Web Applications?
For this question, I have also written a small blog post. It will return all Web Applications with their respective Content Databases: http://sharepointrelated.com/2012/05/28/get-content-databases-per-web-application-one-liner/

7. Why are my Content Databases named WSS_Content_<Guid>?
The moment you create a Web Application, you will be asked to fill in details regarding your content database. By default, SharePoint will fill in this field for you with the following syntax: WSS_Content. If a content database with this name already exists, it will add a GUID to it.

8. What if I want to move my site collection to another Content Database?
In this case, there are 2 scenario’s:
– Move the site collection to another Content Database that is attached to the same Web Application
You can use the Move-SPSite cmdlet to move the site collection.
– Move the site collection to another Content Database that is attached to another Web Application
You can use the Backup-SPSite and Restore-SPSite functionality to accomplish this task.

9. I have multiple site collections in my Content Database, can I use PowerShell to see their sizes?
Yes, you can. You can use the following line of code to check the approximate size of your site collection (From Todd’s blog: Using PowerShell to find site collection size in SharePoint 2010)

Get-SPSite "http://someurl.com&quot; | select url, @{label="Size in MB";Expression={$_.usage.storage/1MB}}

10. Is it possible to maintain the same Content Database for more than one Web Application?
No, this is not possible. A Content Database can only be attached to a single Web Application.

Advertisements

Error using Move-SPSite cmdlet

This post discusses the following error:

Move-SPSite : Cannot insert duplicate key row in object ‘dbo.AllSites’ with unique index ‘Sites_Id’. The duplicate key value is <GUID>.

Let’s say you have 1 Web Application with 2 content databases:
– Database 1
– Database 2

Database 1 contains a site collection with the following URL: http://portal.contoso.com.
For some reason, you moved the site collection using the following cmdlet:

Move-SPSite http://portal.contoso.com -DestinationDatabase "Database 2" -Confirm:$false

image

So far so good. Now let’s say you want to move this site collection back to Database 1.
You would run:

Move-SPSite http://portal.contoso.com -DestinationDatabase "Database 1" -Confirm:$false

This produces the following error:

image

Solution
This is happening because the site collection has not been completely deleted from the content database.
When you run Move-SPSite, the site collection gets moved to the new content database.
However, the site collection in the source Content Database was not completely removed. The site collection "Deleted" flag was set to 1.

This means it is scheduled for deletion. The Gradual site delete timer jobs will delete the site collection from the content database. By default, this timer job runs once a day. To ensure the site is completely deleted, run the timer job. As this timer job runs for each Web Application, make sure you select the correct Web Application

image

After you ran the Gradual Site Delete timer job, you will be able to move the site collection back to Database 1.

Get Content Databases per Web Application one-liner

Another one in the series of one-liners. This one will show you all Web Applications, and all associated Content Databses for these Web Applications:

Get-SPWebApplication | %{Write-Output "`n- $($_.url)"; foreach($cd in $_.contentdatabases){Write-Output $cd.name}}

The output will look similar to this:

image

You can output this to a text file by running the following:

Get-SPWebApplication | %{Write-Output "`n- $($_.url)"; foreach($cd in $_.contentdatabases){Write-Output $cd.name}} >> D:\filename.txt

Get Site Collections per Content Database one-liner

This will be a very short post, as it contains only 1 line of PowerShell code, and is not very hard to understand. It shows you all content databases, and for each of the content databases, it will show you the site collections within this content database.

Get-SPContentDatabase | %{Write-Output "- $($_.Name)”; foreach($site in $_.sites){write-Output $site.url}}

That wasn’t so bad right? Just copy and paste this code into your SharePoint 2010 Management Shell (As administrator), and let PowerShell do your work for you!

image

Now let’s say you want to save this output to a .txt file, you can add “> C:\sitecollections.txt”.

The full command would be:

Get-SPContentDatabase | %{Write-Output "- $($_.Name)”; foreach($site in $_.sites){write-Output $site.url}} > C:\sitecollections.txt

This would create a .txt file with the same information:

image

That’s all there is to it!

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.