SharePoint content database tutorial

In this SharePoint tutorial, we will discuss the SharePoint content database. We will see how to work with the SharePoint content database using PowerShell. We will check:

• Get Content DataBase Size Using PowerShell in SharePoint 2013
• How to Find SharePoint content database name for site collection using PowerShell
• How to get SharePoint content database size using PowerShell
• How to Move SharePoint databases to new SQL server
• Get content database sizes for all web applications in a SharePoint farm using PowerShell
• Get content database sizes for all web applications in a SharePoint farm using PowerShell
• Attach or detach content databases in SharePoint 2013/2016
• Detach content database using SharePoint Central Administration
• Attach SharePoint content database using PowerShell
• Detach SharePoint content databases using PowerShell
• Prevent new sites to be added to content databases in SharePoint

Get Content Database Size using PowerShell in SharePoint 2013

Let us see, how to get content database size using PowerShell in SharePoint 2013. The same PowerShell script we can use to get content database size using PowerShell in SharePoint 2016 or SharePoint 2019.

Scenario: While migrating SharePoint one version to another version we need to know the content DB sizes and total disk space to be required to on new SQL server.

In this article, I would like to walk you through 2 PowerShell scripts.

• The first script will provide all the content databases created/associated with the application server in Size of the DB in GB and the content Databases name in a tabular format.
• The second script will provide all the content database sizes along with the total space required on the new SQL Server to be migrated on.

You can use Visual studio code to run, debug and test PowerShell script.

SharePoint 2013 get content database size using PowerShell Script

The below PowerShell script will get all the content databases names and its size in GB with a tab separation in SharePoint 2013.

Get-SPDatabase | Sort-Object disksizerequired –desc | %{write-hist “`nSize:” (“{0:N2}”-f $($_.disksizerequired/1024mb)) GB “`tDatabase; $($_.Name)”}

This will provide the output as below:

“Get-SPDatabase” is to get all the list of content databases associated with the application server.
https://docs.microsoft.com/en-us/powershell/module/sharepoint-server/Get-SPDatabase?view=sharepoint-ps

“Sort-Object disksizerequired –desc” this is to sort the list with the size of content DB in descending order.

“`n” this character is to create a new line for every content DB

“{0:N2}” –f this helps to format the DB size with 2 decimals. For more formatting options please see the Technical article.

$_.diskspacerequired/1024mbdetermining the size of the content DB and dividing it with 1024 MB which will eventually return the size in GB

Get Content DataBase Size using PowerShell in SharePoint 2013

This PowerShell script will read through each and every content database created/associated with the application server with the help of a foreach loop and sum-up the total size.

Get-SPDatabase | ForEach-Object {$db=0} {$db+=$_.disksizerequired; “`nDB Size =”+ (“{0:N2}” – f ($_.disksizerequired/1024mb)) + “ GB `t`t Content Database= “+ $_.name} {Write-Host “`n Total Storage Space Required=” (“{0:N2}” –f ($db/1024mb)) GB}

This will provide the below output:

This is how to get SharePoint content database size using PowerShell in SharePoint 2013/2016/2019.

How to Find SharePoint content database name for site collection using PowerShell

Let us check out, how to find content database name for site collection using PowerShell in SharePoint 2013/2016/2019. We can easily know which content database one SharePoint site collection is using in SharePoint using PowerShell as well as from SharePoint central administration.

First, we will see how to find the content database name for site collection from SharePoint 2013/2016 central administration.

Open SharePoint 2013/2016 central administration. Then click on Application Management.

Then under Site Collections, click on View all site collections.

In this page you can see the particular site collection and then in the right side you can see the content database name like below:

Using PowerShell also we can be able to know which content database one site collection is using in SharePoint 2013/2016.

You can execute the below PowerShell code in Windows PowerShell ISE.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

(Get-SPSite "http://mypc:29024/sites/HydTraining/").ContentDatabase.name

This is how to find content database name for site collection using PowerShell in SharePoint 2013/2016/2019.

Get SharePoint content database size using PowerShell

Let us see, how to get SharePoint content database size using PowerShell. The same PowerShell script code, we can use to get content database size using PowerShell in SharePoint 2013/2016/2019. We can also use the server object model code to get SharePoint 2013/2016/2019 content database size.

Below is the PowerShell command to get the SharePoint content database size using PowerShell.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name, @{Label =”Size in MB”; Expression = {$_.disksizerequired/1024/1024}}

If you want to download in CSV or .txt format, then you can write code like below:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name, @{Label =”Size in MB”; Expression = {$_.disksizerequired/1024/1024}} >E:\Content_DBsize.csv
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
Get-SPDatabase | Sort-Object disksizerequired -desc | Format-Table Name, @{Label =”Size in MB”; Expression = {$_.disksizerequired/1024/1024}} >E:\Content_DBsize.txt

This is how we can get SharePoint content database size using PowerShell.

Get SharePoint Content database size using SharePoint server object model

Below code, you can use to get content database size using SharePoint server object model code.

string s = string.Empty;
using (SPSite startSite = new SPSite(“http://mypc:29024/sites/SPTraining/”))
{
SPFarm farm = startSite.WebApplication.Farm;
SPWebService service = farm.Services.GetValue<SPWebService>(“”);
foreach (SPWebApplication webApplication in service.WebApplications)
{
foreach (SPContentDatabase contentdatabase in webApplication.ContentDatabases)
{
s += “Name: ” + contentdatabase.Name + “Size: ” + contentdatabase.DiskSizeRequired/(1024*1024)+” MB”+ “\n”;
}
}
}
label11.Text = s.ToString();

This is how to get content database size using PowerShell in SharePoint 2016 and using SharePoint server object model.

Get content database sizes for all web applications in a SharePoint farm using PowerShell

Let us see, how to get content database size for all web applications in a sharepoint farm using PowerShell in SharePoint 2013/2016.

This script allows getting the size for all the content databases available in a SharePoint Farm. The script defines a function (Get-ContentDBSizes) that gets all the Web Applications existing in a SharePoint Farm. For each Web Application, the function also gets the information.

All you got to do is spin up PowerShell on any SharePoint server in the farm and run this:

#############################################################
# Script that gets content databases sizes for all the web applications in a SharePoint Farm
# Parameters: N/A
#############################################################

If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{ Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell }
$host.Runspace.ThreadOptions = "ReuseThread"
#Function that gets the size of all the content databases
function Get-ContentDBSizes
{
try
{
$spWebApps = Get-SPWebApplication -IncludeCentralAdministration
foreach($spWebApp in $spWebApps)
{
#$spWebApp.Name
$ContentDatabases = $spWebApp. WSS_Content_Name (Change here DB name)
foreach($ContentDatabase in $ContentDatabases)
{
$ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2)
$ContentDatabaseInfo= $spWebApp.DisplayName + "," + $ContentDatabase.Name + "," + $ContentDatabaseSize + " GB"
$ContentDatabaseInfo
#Write-Host " * " $spWebApp.DisplayName "-" $ContentDatabase.Name ": " $ContentDatabaseSize " GB"
}
}
}
catch [System.Exception]
{
write-host -f red $_.Exception.ToString()
}
}

Start-SPAssignment –Global
Get-ContentDBSizes > ContentDBs.csv
Stop-SPAssignment –Global
Remove-PsSnapin Microsoft.SharePoint.PowerShell

Once all modification done, Click on Run button on the PowerShell ISE. Like below:

After running successfully we will get result like below:

Open that .CSV file and verified all content DB size, below is the result sheet:

Here, we learned how to get content database sizes for all web applications in a SharePoint farm using PowerShell.

Attach or detach content databases in SharePoint 2013/2016

Let us see how to attach or detach content databases in SharePoint 2013/2016 using PowerShell or by using SharePoint central administration.

We can attach or detach a content database using SharePoint Central Administration or by using PowerShell commands.

Follow the below steps to attach a content database using SharePoint central administration.

First Open SharePoint Central Administration, then click Application Management.

On the Application Management page, in the Databases section, click Manage content databases.

On the Manage Content Databases page, click Add a content database.

This will open the Add Content Database page. Here in this page:

• We need to select a particular web application.
• We need to specify the database server.
• Database Name, And the authentication mode and if you are using Sql Server authentication mode then we need to give the account name and password.

Then Click on OK.

Detach content database using SharePoint Central Administration

Follow the below steps to detach content database using SharePoint central administration.

First Open SharePoint Central Administration, then click Application Management.

On the Application Management page, in the Databases section, click Manage content databases.

Then select the web application for which you want to detach a content database.

Click the content database that you want to detach.

On the Manage Content Database Settings page, select the Remove content database checkbox.

Then click on OK. Then this will ask for Confirmation and then click on OK again.

Attach SharePoint content database using PowerShell

We can also easily attach a SharePoint content database using PowerShell.

First Open PowerShell and run the below command:

Mount-SPContentDatabase "<ContentDb>" -DatabaseServer "<DbServer>" -WebApplication http://SiteName

Detach SharePoint content databases using PowerShell

We can also detach a SharePoint content database using PowerShell in SharePoint.

First Open PowerShell and run the below command:

Dismount-SPContentDatabase "<ContentdBName>"

Prevent new sites to be added to content databases in SharePoint

Now, let us see how to prevent new sites to be added to content databases that grow more in database size in SharePoint 2013/2016.

Suppose the content databases have grown larger than 100 gigabytes (GB). Large content databases can be difficult to back up and restore. And sometimes this causes the application to stop responding when you perform operations that affect entire databases.

Here we can edit the rule definition to prevent new sites from being added to these databases.

We can do this from SharePoint Central Administration.

Open SharePoint Central Administration, On the Monitoring page, in the Health Analyzer section, click Review rule definitions.

Then On the Health Analyzer Rule Definitions page, in the Availability category, click the name of the rule.

In the Health Analyzer Rule Definitions dialog box, click Edit Item, and then select the Repair Automatically checkbox.

Click Save. You can no longer add new sites to databases that exceed 100 GB in SharePoint.

Here, we learned how to attach or detach content databases in SharePoint 2013/2016.

How to Move SharePoint databases to new SQL server

Let us see, the need to move SharePoint Databases to new SQL Server and how to move the SharePoint database to the new SQL Server.

SQL Server is a relational database service on the leading market of the SQL Server engine. It provides various performances, business continuity, scalability with no downtime, data protection, etc.

It is mainly used on the organization platforms for data integration, data analysis, and processing components. It is very stable, popular, affordable, as well as a fast database engine.

Why do users need to move SharePoint database to SQL Server?

SharePoint is a web-based application, which enables an organization to work more professionally by allowing the users to share data, information, and documents. There are various SharePoint products for an organization, which offers a unique set of advantages to users.

However, there is some circumstance that is faced by users while using SQL Server such as – SharePoint has some file size limit because of which it creates a problem in synchronization of the file. It even blocks some file types to be uploaded on SharePoint. It does not have any profile pages for any external content type. It does not work in offline mode; it needs proper internet connectivity to work. Even, it does not have any search integration.

To overcome such shortcomings, many users are moving their data from SharePoint databases to the new SQL Server.

Move SharePoint databases to new sql server

The user can easily move the SharePoint database to a new SQL Server by following the steps given below:

Run CMD in administration mode and type the following command:

c:\windows\system32>cliconfg.exe

Now a client-server-client network utility window will appear, enable the TCP/IP on it. Select Alias >> Add > type the same name for server >> OK

Then Open SharePoint management shell and type a command.

psc:\users\mradwan\get-database

Now the entire database for SharePoint will be listed.

Copy all the GUID and paste it into a notepad file.

Now type a command on SharePoint management shell

psc:\users\mradwan>$db-get-spdatabase

Write the following command and then type an update command

psc:\users\mradwan\$db.ChangeDatabaseInstance<“TFS”

Follow the above two steps for all GUID’s

Now, Start all the SharePoint services that were stopped earlier and Open SQL administration and enter credentials.

Now, you can easily view your entire SharePoint database in SQL Server. The above blog discusses a procedure for migrating SharePoint database to SQL Server with the combined usage of CMD and PowerShell. Users need to use cmdlet for moving the data from one database to a new server however, a set of requirements needs to be fulfilled beforehand to make the procedure feasible.

Here, we learned the need to move SharePoint Databases to a new SQL Server and how to move the SharePoint database to a new SQL Server.

You may like the following SharePoint tutorials:

• Add user to SharePoint group programmatically (JSOM, CSOM, and Rest API)
• SharePoint user custom actions (Designer + Programmatically)
• How to delete a SharePoint site using PowerShell
• SharePoint Online jsom examples
• SharePoint Training Videos FREE (Step by Step Learn SharePoint)
• How to delete a list in SharePoint using PowerShell
• Backup and Restore content database SharePoint 2013
• SharePoint 2010 to SharePoint 2013/2016/2019 Migration Steps
• The database engine instance you selected is not valid for this edition of reporting services
• Shrink SharePoint config database transaction log files

In this tutorial, we discussed about SharePoint content database tutorial. And,

• Get Content DataBase Size Using PowerShell in SharePoint 2013
• How to Find SharePoint content database name for site collection using PowerShell
• Get SharePoint content database size using PowerShell
• Get SharePoint Content database size using the SharePoint server object model
• How to Move SharePoint databases to new SQL server
• Why do users need to move the SharePoint database to SQL Server?
• Move SharePoint databases to new sql server