Get disk storage for SharePoint databases

To get an indication for all database sizes in your SharePoint farm, run the following command. It shows the disk storage in bytes and MB.

image

The PowerShell script used for the output above, can be found below.

Add-PSSnapin Microsoft.SharePoint.PowerShell Get-SPDatabase | sort Name | ft Name, ` @{Name="DiskSizeRequired";Align='Right';Expression={[string]("{0:N0}" -f($_.DiskSizeRequired))}}, ` @{Name="Size(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.DiskSizeRequired/1mb))}}, ` Server -Autosize Get-SPDatabase | sort Name | select Name, DiskSizeRequired ` | measure-object -Property DiskSizeRequired -Sum -Maximum -Minimum -Average ` | ft Count, @{Name="Sum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Sum/1mb))}}, ` @{Name="Maximum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Maximum/1mb))}}, ` @{Name="Minimum(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Minimum/1mb))}}, ` @{Name="Average(MB)";Align='Right';Expression={[string]("{0:N0}" -f($_.Average/1mb))}}

Advertisements

Why can’t I use .Include() with CSOM

While working on the SharePoint Client Browser for SharePoint 2010 and 2013 I wanted to optimize performance by using the .Include() method. Visual Studio did not show the method and the build broke. This post takes about the use of .Include() and  .IncludeWithDefaultProperties() and the reason why Visual Studio did not show/support both methods.

Introduction

Not all properties within the CSOM are loaded by default when using the ClientContext.LoadQuery. This design decision is taken by the Product Team due performance optimization. They did provide an option to extend the properties loaded by using the .Include() or .IncludeWithDefaultProperties() Linq extensions for the SharePoint Client Side Object Model (CSOM).

The example code below shows the use of the .Include() method and loads all lists together with the related fields within 1 request. This optimizes performance by minimizing the overload for a second or even more queries.

The issue with using the .Include() comes when the code is slightly changed! Check the altered code example below!

using System; using System.Collections.Generic; using Microsoft.SharePoint.Client; namespace SharePointCSOMConsole { class Program { static void Main() { ClientContext clientContext = new ClientContext("http://intranet.contoso.com"); IEnumerable<List> lists = clientContext.LoadQuery( clientContext.Web.Lists.Include( list => list.Title, list => list.Hidden, list => list.Fields.Include( field => field.Title, field => field.Hidden))); clientContext.ExecuteQuery(); foreach (List list in lists) { Console.WriteLine("{0}List: {1}", list.Hidden ? "Hidden " : "", list.Title); foreach (Field field in list.Fields) Console.WriteLine(" {0}Field: {1}", field.Hidden ? "Hidden " : "", field.Title); } } } }

Visual Studio not showing .Include()

While working on the SharePoint Client Browser for SharePoint 2010 and 2013 I wanted to optimize performance by using the .Include() method. But my code did not allow me to use the .Include() method. The screenshot below shows Visual Studio source code editor with the missing extension method.

Altered code example, no allowing to use .Include()

Solution

Because I used a different using statement on the top the .Include() method was not shown. After changing the “using SP = Microsoft.SharePoint.Client;” back to “using Microsoft.SharePoint.Client;” the issue was resolved.

Using CSOM and PowerShell to query SharePoint Online or on-premise

Recently I released the SharePoint Client Browser (preview) that provides inside in a remote SharePoint site by using the Client Side Object Model (CSOM). The tool only reads data and shows it in a rich interface. When you want to make changes you have options: 1) build a custom tool and use the CSOM to interact with SharePoint or 2) use PowerShell and CSOM to interact with SharePoint.

This post shows how to use PowerShell and CSOM together to interact and automate configuration changes to your SharePoint. The example script can be used for both SharePoint Online and on-premise deployments.

This PowerShell script will be a new feature in the SharePoint Client Browser. This allows the user to start PowerShell directly from the SharePoint Client Browser and eliminates the need to do the scripting discussed in this post by yourself!

Prerequisites

Besides having a SharePoint site collection somewhere, you need to create a folder on your local machine. This folder needs to have the SharePoint Client Runtime assemblies. These can be downloaded here. Next to the SharePoint Client Runtime you need to place your scripts here (for example: OpenSite.ps1).

Folder (on local machine) with prerequisites

The PowerShell script

The easiest way to get started is creating a new TXT-file in the local folder and rename it to “OpenSite.ps1” (or choose a more appropriate name). Next right click with your mouse on the file and choose Edit. This will open up the Windows PowerShell ISE and you can start editing your script.

Authentication

The script is pretty easy and almost reusable for both SharePoint Online and on-premise, but it has a difference between them. Guest what it is?! The authentication is done differently. Below 3 different authentication examples.

Current user credentials

By not providing any credentials automatically the current user credentials are used.

SharePoint Online

When connecting to SharePoint Online you can use the new SharePointOnlineCredentials class. This makes it a lot easier then with SharePoint 2010.

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($loginname, $pwd)

SharePoint (on-premise) and credentials

Setting the credentials is done via the NetworkCredential class. That looks like this:

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) $ctx.Credentials = New-Object System.Net.NetworkCredential($loginname, $pwd)

The full example script

Below you can find the PowerShell script to use with SharePoint Online. Make sure when using the script you choose the right authentication model (see above). After running this script you can use the CSOM the same way as you would use CSOM in your C# application.

$loc = "C:\Users\Bram\Downloads\SharePoint" # Location of DLL's $siteUrl = "https://contoso.sharepoint.com" $loginname = "bram@contoso.onmicrosoft.com" Set-Location $loc Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll") Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll") Write-Host "Please enter password for $($siteUrl):" $pwd = Read-Host -AsSecureString $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) # Remove or replace line below to change authentication method $ctx.Credentials = New-Object System.Net.NetworkCredential($loginname, $pwd) $web = $ctx.Web $ctx.Load($web) $ctx.ExecuteQuery() Write-Host " Current web title is '$($web.Title)', $($web.Url)"