Getting your arms around the database sizing of your SharePoint farm

SharePoint Database Size Planning

In order to manage your SharePoint farm, and especially for planning for backup/recovery you need to understand data sizing of your farm. Here are the steps you can take to gather the information needed to understand the existing farm and estimate its growth. This will give you a clear understanding of the size of your backups, so you can plan for recovery timeframes, and will also give insights into the rate of growth and on quotas that can govern growth of databases.

Size of all SharePoint Databases

To plan for DR one needs to know the size of all databases to be backed up and restored. This small script will produce a CSV report of the bytes per database attached to the SharePoint farm:

Get-SPDatabase | select name,DiskSizeRequired | convertto-csv | set-content "C:DBsize.csv"

RBS Report

There is no direct mechanism in Central Admin to view RBS configuration. This script will give you a report of the RBS settings throughout your farm:

Get-SPContentDatabase | foreach {$_;
try {
$rbs = $_.RemoteBlobStorageSettings;
write-host "Provider 	Name=$($rbs.GetProviderNames())";
write-host "Enabled=$($rbs.enabled)";
write-host "Min Blob 	Size=$($rbs.MinimumBlobStorageSize)"
}
catch
{write-host -foregroundcolor red "RBS not installed on this database!`n"}
finally {write-host "End`n"}
}

Site Collection size report

It is useful to know the sizes of your Site Collections, and their distribution among your Content Databases. You can report on the size of each Site Collection within each Content DB within a given Web Application with the script below. The output is a CSV (Comma Separated Value) file easily read into Excel. If you have a lot of Site Collections, just convert to a PivotTable, to see the distribution and sizes of Site Collections across Content Databases.

get-spwebapplication http ://SharePoint | Get-SPSite -Limit all | select url,contentdatabase,@{label="Size in GB";Expression={$_.usage.storage/1GB}} | convertto-csv | set-content "C:TEMPDBsize.csv"

Site Collection sizes help inform how to rebalance Content Databases for optimal sizing to allow you to meet your RTO.
One common situation is for MySites to be distributed unevenly across Content Databases, leading to one Content Database being much larger than others. As discussed earlier, managing Content Database sizes is key to meet your RTO.

Quota Report

Setting quotas puts in place limits on Site Collection growth. It also gives the Administrator weekly notification of Site Collections that have exceeded a preset warning size.
This report gives you a list of all the quotas in place across a Web Application:

$webapp = Get-SPwebapplication "http ://SharePoint"
$webapp | get-spsite -Limit ALL | ForEach-Object {
$site = $_;
$site;
$site.quota;
}
$site.dispose()
$webapp.dispose()

What you want to look for first are Site Collections that have no quotas. These represent opportunities for unconstrained growth without notification that could result in Content Database growth that exceeds your RTO targets.

Limiting Library Version Storage across your SharePoint farm

There are situations where documents are frequently edited. Each edit creates a new version in SharePoint. In SP2010, each version consumed the full disk space, with no optimization for managing deltas. In SP2013, one of the benefits of Shredded Storage is that it optimizes storage usage for multiple similar versions of not just Office XML (Office 2010/2013) documents but also other filetypes like PDFs and image files. It does this by working out and storing only the file differentials. Even with Shredded Storage, you can limit the number of versions retained on document edits. Here’s how to do this across your farm. Let’s limit major versions to three, and minor versions to five:

$spWebApp = Get-SPWebApplication http ://SharePoint
for ($Si=0; $Si -lt $spWebApp.Sites.count; $Si++)
{
$site = $spWebApp.Sites[$Si];
for ($Wi=0; $Wi -lt $site.AllWebs.count; $Wi++)
{
$web = $site.AllWebs[$Wi];
for ($Li=0; $Li -lt $web.Lists.count; $Li++)
{
$List = $web.Lists[$Li];
if ($list.EnableVersioning)
{
$list.MajorVersionLimit = 3
}
if ($list.EnableMinorVersions)
{
$list.MajorWithMinorVersionsLimit = 5
}
$list.Update()
}
$web.dispose()
}
$site.dispose()
}
$spWebApp.dispose()

Reporting on SharePoint MySite distribution by Content Database

Reporting on MySite Content Databases

Knowing how sites are distributed among Content Databases is key, such as knowing which Content Database to restore for a given user.

Wouldn’t it be nice to see the breakdown of MySites, which belong to a given Content Database, and the total size of each Content Database? Here’s a script that generates this useful report:

$DBs = Get-SPWebApplication http://MySites | Get-SPContentDatabase
foreach ($db in $DBs)
{
Write-Host -ForegroundColor DarkBlue "DB Name: $($db.Name)"
$siz="{0:N3}" -f ($db.disksizerequired/1GB)
write-host -ForegroundColor DarkBlue "DB Size: $($siz) GB"
Write-Host "========"
$db | Get-SPSite -Limit all
Write-Host " " 
}

Diagnosing and rectifying SQL Transaction Log Growth

Diagnosing and rectifying SQL Transaction Log Growth

Ever wonder why your transaction logs can grow out of control? First place to check is whether the Database is set to Full or Simple mode.   Unless you are running in Simple Mode, backups need to be taken at least hourly to reduce the accumulating Transaction Logs.

The database keeps its own sys log that can tell you what’s preventing Transaction Logs from being cleared; here’s a query to run:

SELECT TOP 1000 [name]
,[log_reuse_wait]
,[log_reuse_wait_desc]
,[is_date_correlation_on]
FROM [master].[sys].[databases]

First thing to try is to Shrink the database, assuming you are already in Simple Recovery Mode:

USE dbname
CHECKPOINT
--First parameter below is the fileno for the log file, often 2. Check the sys.database_files
--Second parameter is the target size in MB.
DBCC SHRINKFILE(2, 500)
DBCC SQLPERF(LOGSPACE)
DBCC LOGINFO

Logfile space is not automatically recovered.  The SQL to Truncate and recover the log space is to run the following against the LDF file:

DBCC SHRINKFILE
(
logical file_name
target_size in MB ], TRUNCATEONLY
)
WITH NO_INFOMSGS