Monthly Archives: July 2014

SharePoint database growth settings

This is a basic topic but one that crops up time and time again.

By default SharePoint will create databases with settings to grow 1 MB at a time. That means that if you add a 5 MB file the database will grow 5 times to fit it in. If you add a 250 MB file (the default largest size file for a 2013 farm) that means a worrying 250 growth operations will be needed.

Why is growth bad?
During a growth operation the database is completely unresponsive. That means that any access at all, read only or not, will have to wait for the growth to complete. That will result in slower responses for users and reduce the number of users that your environment can support at any one time. As such growth is bad.

How long does growing take
It varies depending on your hardware. In my test environment, a Hyer-V machine with a dynamically growing VHDX on an SSD I got the following numbers. I’m going to put up a post on how I got these numbers sometime soon, i’ll update this post once I’ve done so.

Growth Size / MB Average time / ms Average time per MB
1 13.40 13.40
10 46.11 4.61
100 328.17 3.28
1000 2480.67 2.48

If we plot that data on a chart we get a clearer image

Graph of autogrowth duration

Graph of autogrowth duration


The duration for growth operations scales with the size, however the change isn’t quite linear, there is an overhead to each growth that makes a single larger growth more efficient. As such it’s best to grow as few times as possible with larger individual growth operations.

On the other hand as you can see a single growth operation can take a significant amount of time. Growing by 1 GB took around 2 and a half seconds. That is a significant delay and whilst it will be infrequent your users may well notice, especially in a heavily used site.

For a 250 MB file the 1 MB growth rate would take 3.25 seconds. A 10 MB growth rate gives 1.15, 100 MB comes in at 0.98 and 1000 MB at 2.48 seconds (but you only have to grow every fourth item).

Category Example Growth Rate
Default 1 MB
Max File Size 250 MB
Large Fixed 1000 MB
Nightly 2000 MB
Pre-grow N/A

Default

This is more or less the worst possible choice. Every time a database grows it needs to lock the entire system. Whilst that might only be for a short time for each one the total time is excessive. As we saw this will take around 3.25 seconds to perform all the growth needed for a 250 MB file. The only redeeming feature of this setting is that it is very efficient for storage space.

Max File Size

By setting the file growth rate to the web application maximum file size you can ensure that only a single growth operation will happen for any file upload. This gives a relatively low individual growth duration which means the disruption for a single file will be as small as possible. On the other hand it will result in a lot more individual growth operations than a larger size. That means that in a highly used site you may have statistically significant and detectable amounts of downtime.

Large Fixed

By setting the autogrowth setting to an arbitrarily large size, eg. 1 GB, you can minimise the number of growth operations that need to occur. This has a much lower total disruption than the Default settings or setting to the maximum file size but when it does occur the delay can be significant. This is a popular setting.

Nightly Growth

As an alternative to autogrowth settings you can grow the databases ahead of the rate that users put data into them. To do this your SQL management team would set an alert on the database fill rate to ensure that there is more free space available than can reasonably be expected to be used in a day. If the database drops below that point then your SQL admins will grow the databases manually during the next scheduled downtime or period of low load.
This avoids/minimises any of the aforementioned disruption to users when growth occurs but requires an alert and capable SQL team. If your SQL team miss the alert then either a Max File Size or Large Fixed policy should be in place as backup.

Pre-grow

The most performant option is to grow your databases to their final size on creation. This requires a highly predictable environment with site collection quotas in use to prevent the databases growing beyond their expected size. It’s also a very storage-space inefficient way to manage your databases.

Summary

Which option you should use depends on where you’re putting SharePoint in. My preference is to set the growth settings to the maximum file size for the web application it is attached to. This provides the ideal blend of minimising total growth time whilst avoiding large delays when larger growth operations are needed.
If you have a good SQL team behind you to grow the databases nightly then that is an even better way to avoid disruption to users. However that does require the SQL team to be available and able to assist, if this isn’t available or you’re in a really high usage environment then you should pre-grow the databases up front and never have to suffer growth again.

SharePoint Surveys and the mystery of the missing partial response

Note: this applies to SharePoint 2010 and 2013, it is not viable for SharePoint online / Office 365.

Fun fact, SharePoint, if you’ve got branching logic in your survey, allows you to save your response to a survey!

This is great news. However on TechNet someone asked if it were possible to find these incomplete posts to remind the user that they haven’t finished?

Normally you’d assume that all you’d need to do is log in with a site collection admin account and you’d magically be able to see all the responses, even those that haven’t been completed. However in this case you’d be wrong. Even with Site Collection admin rights those incomplete responses are hidden from you.

Now that data is still in the SharePoint list and rather than mess about with permissions and settings, which could have unforeseen consequences, let’s see if we can’t pull it out of the list with PowerShell?

The first thing to do is see if we can get the real number of items from the survey. The easiest way to do that is to check the item count.

$web = Get-SPWeb "URL"
$survey = $web.Lists["SurveyName"]
Write-Host "Items in the survey : " $survey.ItemCount

That gives you the number of items, which you can compare to those visible. In my case I had one completed and one partial, leading to a total count of two. Which the script agreed with.

When you look at the object in PowerShell you can see that there’s a few fields that are different between a completed and a partial response. In this case we have two that are of interest.

Image showing different HasPublishedVersion and Level for two items.

The differences between a visible, completed item (1) and an incomplete item (2)

Of the two i’m going to use the ‘HasPublishedVersion’ field. Live the ‘Level’ field it will become true as long as a version has been completed, however unlike Level it will remain true even if the user starts to edit it later and somehow manages to do a partial save.

Let’s extend our script to list out the number in each group, then list the users who created them.

$web = Get-SPWeb "http://portal.tracy.com/sites/GD/"
$survey = $web.lists["Survey"]
$unPublishedEntries = $survey.Items | ? {-not $_.HasPublishedVersion}

Write-Host "Surveys in list: " $survey.ItemCount
Write-Host "Of which entries are incomplete: " $unPublishedEntries.Count

Foreach ($entry in $unPublishedEntries)
{
    Write-Host "User: {0} has not completed their entry" -f $entry["Created By"]
}

And voila, my results give us:

PowerShell results showing the results from a script

Printout from script

Cataloging Choice Columns

It’s been a while but it’s time for a new post.

Someone asked on TechNet () how to get a summary of all the custom choice columns and the options they have. I didn’t have the time to put it together at that moment but I thought It’d be a good exercise.

This will work for 2010 or 2013 but will not work for Office 365 or SharePoint online. For that a different approach would be needed.

“I inherited a SharePoint 2010 Foundation site that contains about 40 custom columns and about 10 of those custom columns are of the type “Choice”. Is there a way using Powershell or something else to export to a .csv file a list of the custom columns and if they are the type “choice” to show the list of what the various choices are for each column?”

So, let’s assume that we’re only interested in the site columns. To do that we’ll have to grab the SPWeb object and loop through the columns there that are of the appropriate type and list them out.

#Get the web
$web = Get-SPWeb "http://sharepoint.domain.com/sites/sitecollection/subsite"

#Get the columns (note, these are named fields)
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Loop through each choice and print the name
foreach ($entry in $columns)
{
Write-Host ("Field Name: {0}" -f $entry.InternalName)
#Loop through the choices and print those out
foreach ($choice in $entry.Choices)
{
Write-Host ("  Choice: {0}" -f $choice)}
}

From here

That’ll list out the columns to the screen but it’s not a great solution. It’s printing out too many columns, it’s also just printing them to the screen. We need it to serialise this into a format that we can use.

Let’s start with serialisation.

There’s loads of ways to do this but my preference is to create custom objects to contain the information we collect, then assign them to an array which we can process later.

For a quick guide to PSObjects have a look here
So, after changing the write-hosts to write verbose and putting in our custom objects we get this!

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice"}

#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Choices" = $choicesArray
                        }
}

Which actually makes things worse as we no longer get any results! Let’s add that in some xml work. I’m still not entirely happy with the way PowerShell and XML work together so this example is a bit clunky but it works.

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#loop through the results and genearate an xml object
foreach ($column in $ColumnDetailsArray)
{
    #Create an element to hold the top level item
    $columnElement = $xml.CreateElement("Choice")
    $columnElement.SetAttribute("Name", $column.Name) 

    #Loop through the choices and add entries for each
    foreach ($choice in $column.Choices)
    {
        $choiceElement = $xml.CreateElement("Choice")
        
        #Note that you need this Pipe Out-Null to prevent it writing to the console
        $choiceElement.InnerText = $choice
        $columnElement.AppendChild($choiceElement) | Out-Null
    }
    #Once it's built add the element to the root node
    $xml.root.AppendChild($columnElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

So, this now dumps out the data we’ve asked for but it also dumps out all the pre-packaged columns. This is a problem that isn’t easily fixed, there isn’t an ‘OOTB’ flag on fields but there are a few we can use to filter them out.

If we grab a column from the results and run Get-Members on it there are a couple of fields that should be useful for filtering the results:

Sealed – This shows if the column is not supposed to be edited by human hands. Note that this could give false negatives in scenarios where a column has been deployed via the CTHub which I think seals columns (it definitely seals Content Types) as it pushes to consuming site collections

Hidden – Not relevant in this case but often handy. In this case we’ll filter out groups that are part of the ‘_hidden’ group earlier.

So if we now add that criteria to the earlier $columns filtering process we get

$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }

But that’s still not perfect, so instead of filtering the terms out right now let’s make it a bit more useful first. When you look at columns in SharePoint they are organised in Groups. We can see those properties in PowerShell and group our elements using that field.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}

#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

#Get a unique list of the groups in use in the site
foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group
    Write-Host "Group name: " $groupText
    $groupElement = $xml.CreateElement("Group")
    $groupElement.SetAttribute("Name", $groupText)
    
    #loop through the results and add them to the xml object
    foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
    {
        #Create an element to hold the top level item
        $columnElement = $xml.CreateElement("Choice")
        $columnElement.SetAttribute("Name", $column.Name)

        #Loop through the choices and add entries for each
        foreach ($choice in $column.Choices)
        {
            $choiceElement = $xml.CreateElement("Choice")
        
            #Note that you need this Pipe Out-Null to prevent it writing to the console
            $choiceElement.InnerText = $choice
            $columnElement.AppendChild($choiceElement) | Out-Null
        }
        #Once it's built add the element to the root node
        $groupElement.AppendChild($columnElement)  | Out-Null
    }
    $xml.root.AppendChild($groupElement)  | Out-Null
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

Of course once you have the group name you can filter those options out by using a blacklist of groups to avoid reporting on.


Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue

#BlackList Group Names
#These are the known groups you get in a non publishing team site:
$blackList = @(
    "_Hidden",                                                                         
    "Base Columns",                                                                    
    "Content Feedback",                                                   
    "Core Contact and Calendar Columns",                                               
    "Core Document Columns",                                                         
    "Core Task and Issue Columns",                                                          
    "Custom Columns",                                                            
    "Display Template Columns",                                                          
    "Document and Record Management Columns",                                                
    "Enterprise Keywords Group",                                                             
    "Extended Columns",                                                             
    "JavaScript Display Template Columns",                                                   
    "Reports",                                                                  
    "Status Indicators"
)

#Get the web
$web = Get-SPWeb "http://portal.tracy.com/sites/cthub"

#Get the columns (note, these are named fields) 
#Also filter out the sealed fields
$Columns = $web.Fields | ? { $_.TypeAsString -eq "Choice" -and 
        -not $_.Sealed -and $_.Group -ne "_Hidden"
    }


#Print out the number of columns
Write-Host "Number of columns found: " $Columns.Count

#Create empty array
$ColumnDetailsArray = @()

#Loop through each choice column and create an object to hold it
foreach ($entry in $columns)
{
    $choicesArray = @()
    Write-Verbose ("Field Name: {0}" -f $entry.InternalName)
    
    #Loop through the choices and print those out
    foreach ($choice in $entry.Choices)
    {
        #Add each choice to the (local) array
        Write-Verbose ("  Choice: {0}" -f $choice)
        $choicesArray += $choice
    }
    #Create a result object to hold our data
    $ColumnDetailsArray += New-Object PSObject -Property @{
                        "Name" = $entry.InternalName
                        "Group" = $entry.Group
                        "Choices" = $choicesArray
                    }
}
#Create a starter XML for the system to work with
[xml] $xml = "<root><summary rundate='{0}' web='{1}'/></root>" -f 
    (Get-Date -f ddMMyyyy), 
    $web.Title

foreach ($group in $ColumnDetailsArray | select Group -Unique)
{
    $groupText = $group.Group

    #Check to see if the group name is in our blacklist
    if (-not $blackList.Contains($groupText))
    {
        Write-Verbose "Group name: " $groupText
        $groupElement = $xml.CreateElement("Group")
        $groupElement.SetAttribute("Name", $groupText)
    
        #loop through the results and genearate an xml
        foreach ($column in $ColumnDetailsArray | ? {$_.Group -eq $groupText})
        {
            #Create an element to hold the top level item
            $columnElement = $xml.CreateElement("Choice")
            $columnElement.SetAttribute("Name", $column.Name)

            #Loop through the choices and add entries for each
            foreach ($choice in $column.Choices)
            {
                $choiceElement = $xml.CreateElement("Choice")
        
                $choiceElement.InnerText = $choice
                #Note that you need this Pipe Out-Null to prevent it writing to the console
                $columnElement.AppendChild($choiceElement) | Out-Null
            }
            #Once it's built add the element to the root node
            $groupElement.AppendChild($columnElement)  | Out-Null
        }
        $xml.root.AppendChild($groupElement)  | Out-Null
    }
    else
    {
        Write-Verbose "Group skipped:" $groupText
    }
}
$xml.Save("C:\ResultFolder\ColumnSummary.xml")

And there you have it. A working report that will summarise all custom choice columns in a SPWeb object and save them in an XML file.