SharePoint Archives - Bart's Hideout

Cleaning Workflow History in SharePoint

Saturday, 24 February, 2018Tuesday, 13 March, 2018Bart

A lot of organizations that use SharePoint, use the platform to automate business processes using workflows. But little organizations are aware that because of these workflows, your SharePoint environment needs to be maintained a little more than usual. Almost all organizations which use a lot of workflows start having performance issues. SharePoint is starting to get slow, workflows are starting to show weird startup behaviors or don’t start at all. Those kind of things. And they don’t know why this is happening. One of the causes for this are probably the workflow history lists.

Which lists? Workflow history? We don’t have such lists.
Yes you do, but you don’t know it, because they are hidden! The comments and events you see when you look at a started workflow… these are stored in the workflow history list. Because those lists are hidden, most of the organizations who never had to deal with these issues, don’t know these lists exist and so, those lists grow and grow and grow. Up to a point it starts to get problematic.

To give an example, I had a case where the largest workflow history list contained 24 million items. For the entire environment, the combined size of all workflow history was 39 million items. That’s a lot of history. And honestly… nobody cares about this information. It’s only used in the event something goes wrong in a workflow and you need to find out why. But in highly regulated or audited environments, this history data can be important.

If you use Nintex Workflow, you can purge those lists using a single command. But if you try to do this on a list that contains millions of items, chances are that you will run into issues. The list has become too big. And again… purging the data is not always possible due to policies.

Now what? You will probably start searching for a solution… in the end, it’s just a SharePoint list like any other list, right? Online, you find some hints on how to approach those lists:

Easiest option… create a new list, change the workflows to use that new list and just delete the old one. That’s the quickest way to deal with this. But then you will lose all history. Each history list item has an event type from 1 to 11. Suppose you want to retain the events which hold a record of task outcomes. Deleting the list isn’t an option anymore and you need to find a way to selectively delete list items.
Create a script that deletes the items. Well, seems logical to do this. But you need to find the most optimal way of deleting items. Ever deleted SharePoint items in large lists? If you have, you know that SharePoint takes it time to do this. In a specific case I had a delete frequency of 1 item per 9 seconds, for a list of 32000 items. You do the math… that’s 80 hours. Which is a lot. Imagine you have to do this on a list of 24 million items. Best case… it takes 9 seconds per item. That’s 2500 days! Or almost 7 years! Completely insane.

So, out of options?

Well no. If you need to retain specific items on that list, deleting individual list items is still an option. But you need to use a different approach.

Instead of iterating over the complete collection of items in a list and delete one by one, you can use a batch processing method which exists on the SPWeb object. This batch processing method accepts an XML structure that contains “methods”. Each method is an instruction to delete an item in a list.

<Batch>
  <Method ID='ID1'>
    <SetList>bc4e6b96-165c-4261-b606-0f8d5e12b4d0</SetList>
    <SetVar Name='ID'>123</SetVar>
    <SetVar Name='Cmd'>Delete</SetVar>
  </Method>
</Batch>

<Batch>

<SetVar Name='Cmd'>Delete</SetVar>

</Method>

</Batch>

Each method contains the GUID of the list you are targeting, the ID of the item, and a command. In our case “Delete”.

Once you have assembled this structure, you pass it as a parameter to the ProcessBatchData method on the SPWeb object and SharePoint will perform all of the methods in batch.

To give you an idea on the performance of deleting items using this method. It deleted 215000 items in 16 hours. Compare this to 32000 items in 80 hours. That’s a huge improvement.

How would you practically do this?

Well, you use a CAML query to get a bunch of items from your history list and you assemble your batch xml for these items. Once it has been processed, you repeat the query and do it again… until you run out of items.

Here’s an example. The script below is going to remove all list items which have an event type of 0, 4, 5 or 11. The query returns 2000 items at a time and assembles the required batch xml for these 2000 items. The “ListItemCollectionPosition” is used to know when we are at the end of the list and out of items to delete. When this is null after you execute a query, there are no more items to query.

$WebUrl = "http://teamsites.westeros.local"
$listTitle = "NintexWorkflowHistory"
[int[]]$events = 0,4,5,11
$queryString = @"
<Where>
   <In>
      <FieldRef Name='Event'/>
      {0}
   </In>
</Where>"
"@

# Get the web
$Web = Get-SPWeb $WebUrl -ea SilentlyContinue
if($Web -eq $null)
{
   Write-Error "The web with URL '$WebUrl' was not found, halting execution."
   break
}

# Get the list
$list = $Web.Lists["$listTitle"]
if ($list -eq $null)
{
   Write-Error = "The list '$listTitle' was not found on '$WebUrl', halting execution."
   break
}

# Assemble query & command
$query = New-Object Microsoft.SharePoint.SPQuery
$query.RowLimit = 2000

$eventTypes = "<Values>"
foreach ($event in $events)
{
   $eventTypes += "<Value Type='WorkflowEventType'>$event</Value>"
}
$eventTypes += "</Values>"

$query.ViewFields = "<FieldRef Name='ID' /><FieldRef Name='Event'/>"
$query.ViewFieldsOnly = $true
$query.Query = [string]::Format($queryString,$eventTypes)

$listid = $list.id
[System.Text.StringBuilder]$batchXml = New-Object System.Text.StringBuilder
$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>") | Out-Null
$command = [string]::Format("<Method><SetList>{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>",$listid,"{0}")

do
{
   $listItems = $list.GetItems($query)
   $query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
   foreach ($item in $listItems)
   {
      if ($item -ne $null)
      {
         $batchXml.Append([string]::Format($command,$item.id.Tostring())) | Out-Null
      }
   }
   $batchXml.Append("</Batch>") | Out-Null
   $web.ProcessBatchData($batchXml.ToString()) | Out-Null
   [System.Text.StringBuilder]$batchXml = New-Object System.Text.StringBuilder
   $batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>") | Out-Null
}
while ($query.ListItemCollectionPosition -ne $null)
$Web.Dispose()

$WebUrl = "http://teamsites.westeros.local"

$listTitle = "NintexWorkflowHistory"

[int[]]$events = 0,4,5,11

$queryString = @"

<Where>

<In>

{0}

</In>

</Where>"

# Get the web

$Web = Get-SPWeb $WebUrl -ea SilentlyContinue

if($Web -eq $null)

{

Write-Error "The web with URL '$WebUrl' was not found, halting execution."

break

}

# Get the list

$list = $Web.Lists["$listTitle"]

if ($list -eq $null)

{

Write-Error = "The list '$listTitle' was not found on '$WebUrl', halting execution."

break

}

# Assemble query & command

$query = New-Object Microsoft.SharePoint.SPQuery

$query.RowLimit = 2000

$eventTypes = "<Values>"

foreach ($event in $events)

{

$eventTypes += "<Value Type='WorkflowEventType'>$event</Value>"

}

$eventTypes += "</Values>"

$query.ViewFields = "<FieldRef Name='ID' /><FieldRef Name='Event'/>"

$query.ViewFieldsOnly = $true

$query.Query = [string]::Format($queryString,$eventTypes)

$listid = $list.id

[System.Text.StringBuilder]$batchXml = New-Object System.Text.StringBuilder

$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>") | Out-Null

$command = [string]::Format("<Method><SetList>{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>",$listid,"{0}")

{

$listItems = $list.GetItems($query)

$query.ListItemCollectionPosition = $listItems.ListItemCollectionPosition

foreach ($item in $listItems)

{

if ($item -ne $null)

{

$batchXml.Append([string]::Format($command,$item.id.Tostring())) | Out-Null

}

$batchXml.Append("</Batch>") | Out-Null

$web.ProcessBatchData($batchXml.ToString()) | Out-Null

[System.Text.StringBuilder]$batchXml = New-Object System.Text.StringBuilder

$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>") | Out-Null

}

while ($query.ListItemCollectionPosition -ne $null)

$Web.Dispose()

Running this on a list with 24 million items still requires a lot of time. And you might question the importance of retaining events for such lists… but in any case, you now have a faster way of deleting items in a SharePoint list.

In the end, you need to avoid getting in such a situation in the first place. You can do this by establishing some governance policies regarding workflows and create some routines to enforce those policies by performing maintenance on a continuous basis. That way, you will be able to keep those history lists under control.

Gaining insights in your Nintex Workflow Data

Wednesday, 25 October, 2017Bart

Working with workflows in SharePoint is always a challenging task. Especially when you are an administrator and people start complaining about performance issues, workflows that don’t start or resume, and so on. Using Nintex Workflow doesn’t change anything, but it gives you a bit more leverage.

The first thing I always do, is try to get some insights in the size of the issue. So, we’re talking about data. How much workflow data is involved. When Nintex is involved, there are 2 places where I’m going to look:

Workflow History Lists (SharePoint)
WorkflowProgress table (SQL Database)

Nintex has an article on both topics which I can definately recommend.

https://support.nintex.com/SharePoint/Forms/SharePoint_Maintenance_for_Nintex_Workflow

This article links to a PDF which explains how to maintain the WorkflowProgress table. You can find that PDF here.

In this PDF, you will find a SQL query which queries this table and the WorkflowInstance table to give you an insight in the sheer amount of data which is stored and which might be the root cause of your issues with workflows.

Just for you lazy people, here’s the query:

SELECT I.WorkflowName, 
       I.WorkflowInstanceID, 
       I.SiteID, 
       I.WebID, 
       I.ListID, 
       I.ItemID, 
       I.WorkflowInitiator,
       I.WorkflowID, 
       I.State, 
       COUNT(P.WorkflowProgressID) as ActionCount
  FROM WorkflowInstance I INNER JOIN
       WorkflowProgress P ON I.InstanceID = P.InstanceID 
 GROUP BY I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State order by COUNT
(P.WorkflowProgressID) DESC

SELECT I.WorkflowName,

I.WorkflowInstanceID,

I.SiteID,

I.WebID,

I.ListID,

I.ItemID,

I.WorkflowInitiator,

I.WorkflowID,

I.State,

COUNT(P.WorkflowProgressID) as ActionCount

FROM WorkflowInstance I INNER JOIN

WorkflowProgress P ON I.InstanceID = P.InstanceID

GROUP BY I.WorkflowName, I.WorkflowInstanceID, I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State order by COUNT

(P.WorkflowProgressID) DESC

This gives you a lot of information which you can use in your remediation strategy.
Just export that information to a CSV and load it in Excel and you can have the following information at hand in no time.

workflow data

This is a table which gives you, per Nintex content database, the amount of records in the workflowProgress table (ActionCount) and the number of workflow instances involved. And it splits up this information for the different states workflows can be in (running, completed, canceled, error). Whoever came up with the concept of Pivot tables… thank you! 🙂

While this query is great for getting that information and report on it, it’s not that useful for people who are responsible for creating or maintaining those workflows. Look at the query. It gives you the site, web, list and even item that’s involved. But those are the ID’s. You still need to resolve these to a URL or title of a list to be useful for anyone who’s not an administrator on SharePoint and knows his way around in PowerShell.

You could customize this query and include the SharePoint content databases in there to get you the needed information but you know you shouldn’t touch those databases in SQL! So, that’s not an option.

I decided to make my life a little less complicated and use PowerShell to solve it.
The script below is going to execute the query above (with some minor adjustments to get some extra information) and after getting the results, it will get the corresponding URL’s for site and web, and the title of the list (if needed). All of this information is exported to a CSV.

<# 
.SYNOPSIS 
    Get Action Count and instance statistics for a Nintex Content Database 
    
.DESCRIPTION 
    Get Action Count and instance statistics for a Nintex Content Database. 
    
.NOTES 
    File Name: Get-WorkflowProgress.ps1 
    Author   : Bart Kuppens 
    Version  : 1.0 
    
.PARAMETER DBServer 
    Specifies the name of the database server where the Nintex content database is located. 
    
.PARAMETER DBName 
    Specifies the name of the Nintex content database. 
#> 
[CmdletBinding()] 
param( 
    [parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false)] 
    [string]$DBServer, 
    [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false)] 
    [string]$DBName 
) 

function Run-SQLQuery ([string]$SqlServer, [string]$SqlDatabase, [string]$SqlQuery) 
{ 
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
    $SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDatabase;Integrated Security=True" 
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
    $SqlCmd.CommandText = $Sqlquery 
    $SqlCmd.Connection = $SqlConnection 
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
    $SqlAdapter.SelectCommand = $SqlCmd 
    $DataSet = New-Object System.Data.DataSet 
    $SqlAdapter.Fill($DataSet) 
    $SqlConnection.Close() 
    $DataSet.Tables[0] 
} 

$query = @" 
    SELECT I.WorkflowName, 
           I.WorkflowInstanceID, 
           CONVERT(VARCHAR(24), I.StartTime, 120) as StartTime, 
           I.SiteID, 
           I.WebID, 
           I.ListID, 
           I.ItemID, 
           I.WorkflowInitiator, 
           I.WorkflowID, 
           I.State, 
           CONVERT(VARCHAR(24),MAX(P.TimeStamp),120) as LastActionDate, 
           COUNT(P.WorkflowProgressID) as ActionCount 
      FROM WorkflowInstance I INNER JOIN 
           WorkflowProgress P ON I.InstanceID = P.InstanceID 
     GROUP BY I.WorkflowName, I.WorkflowInstanceID, CONVERT(VARCHAR(24), I.StartTime, 120), I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State 
     ORDER BY I.SiteID, I.WebID, I.ListID ASC 
"@ 

Write-Progress -Activity "Getting WorkflowProgress Data" -Status "Running SQL Query" -PercentComplete 100 

$data = Run-SQLQuery -SqlServer $DBServer -SqlDatabase $DBName -SqlQuery $query 

$siteUrl = "" 
$webUrl = "" 
$listTitle = "" 
$DataArray=@() 
$SiteArray = @{} 
$WebArray = @{} 
$ListArray = @{} 

if ($data.Count -gt 0) 
{ 
    if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue) -eq $null) 
    { 
        Add-PSSnapin Microsoft.SharePoint.PowerShell 
    } 
    
    $i = 0 
    $nbrRows = $data.Count 
    $percComplete = 0 
    $percRow = [Math]::Floor(100 / $nbrRows) 
    foreach ($row in $data) 
    { 
        $i++ 
        $percComplete += $percRow 
        Write-Progress -Activity "Processing Data" -Status "Record $i/$nbrRows" -PercentComplete $percComplete 
        if ($row.SiteID -ne $null) 
        { 
            # Check if we already processed this site before 
            if ($SiteArray.ContainsKey($row.SiteID)) 
            { 
                $siteUrl = $SiteArray.Get_Item($row.SiteID) 
            } 
            else 
            { 
                $site = Get-SPSite $row.SiteID -ea SilentlyContinue 
                if ($site -eq $null) 
                { 
                    $siteUrl = "$($row.SiteID) (DELETED)" 
                } 
                else 
                { 
                    $siteUrl = $site.Url 
                } 
                $SiteArray.Set_Item($row.SiteID,$siteUrl) 
            } 
            
            # Check if we already processed this web before 
            if ($WebArray.ContainsKey($row.WebID)) 
            { 
                $webUrl = $WebArray.Get_Item($row.WebID) 
            } 
            else 
            { 
                if ($site -eq $null) 
                { 
                    $webUrl = "$($row.WebID) (DELETED)" 
                } 
                else 
                { 
                    $web = $site.AllWebs | ? {$_.Id -eq $row.WebID} 
                    if ($web -eq $null) 
                    { 
                        $webUrl = "$($row.WebID) (DELETED)" 
                    } 
                    else 
                    { 
                        $webUrl = $web.Url 
                    } 
                } 
                $WebArray.Set_Item($row.WebID,$webUrl) 
            } 
            
            # Check if we already processed this list before 
            if ($row.ListID -ne "00000000-0000-0000-0000-000000000000") 
            { 
                if ($ListArray.ContainsKey($row.ListID)) 
                { 
                    $listTitle = $ListArray.Get_Item($row.ListID) 
                } 
                else 
                { 
                    if ($web -eq $null) 
                    { 
                        $listTitle = "$($row.ListID) (DELETED)" 
                    } 
                    else 
                    { 
                        $list = $web.Lists | ? {$_.Id -eq $row.ListID} 
                        if ($list -eq $null) 
                        { 
                            $listTitle = "$($row.ListID) (DELETED)" 
                        } 
                        else 
                        { 
                            $listTitle = $list.Title 
                        } 
                    } 
                    $ListArray.Set_Item($row.ListID,$listTitle) 
                } 
            } 
            
            if ($web -ne $null) 
            { 
                $web.Dispose() 
            } 
            
            if ($site -ne $null) 
            { 
                $site.Dispose() 
            } 
            $state = "" 
            switch ($row.State) 
            { 
                2 { $state = "Running" } 
                4 { $state = "Completed" } 
                8 { $state = "Cancelled" } 
                64 { $state = "Errored" } 
            } 
            
            $item = @{ 
                "WorkflowName" = $row.WorkflowName 
                "WorkflowInstanceID" = $row.WorkflowInstanceID 
                "StartTime" = $row.StartTime 
                "Site" = $siteUrl 
                "Web" = $webUrl 
                "List" = $listTitle 
                "ItemID" = $row.ItemID 
                "WorkflowInitiator" = $row.WorkflowInitiator 
                "WorkflowID" = $row.WorkflowID 
                "State" = $state 
                "LastActionDate" = $row.LastActionDate 
                "ActionCount" = $row.ActionCount 
            }   
            $DataRow = New-Object PSObject -Property $item 
            $DataArray += $DataRow 
        } 
    } 
    $DataArray | Export-Csv -NoTypeInformation -Path wflProgress.csv -Delimiter ";" 
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

.SYNOPSIS

Get Action Count and instance statistics for a Nintex Content Database

.DESCRIPTION

Get Action Count and instance statistics for a Nintex Content Database.

.NOTES

File Name: Get-WorkflowProgress.ps1

Author : Bart Kuppens

Version : 1.0

.PARAMETER DBServer

Specifies the name of the database server where the Nintex content database is located.

.PARAMETER DBName

Specifies the name of the Nintex content database.

[CmdletBinding()]

param(

[parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false)]

[string]$DBServer,

[parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false)]

[string]$DBName

)

function Run-SQLQuery ([string]$SqlServer, [string]$SqlDatabase, [string]$SqlQuery)

{

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDatabase;Integrated Security=True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $Sqlquery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

$DataSet.Tables[0]

}

$query = @"

SELECT I.WorkflowName,

I.WorkflowInstanceID,

CONVERT(VARCHAR(24), I.StartTime, 120) as StartTime,

I.SiteID,

I.WebID,

I.ListID,

I.ItemID,

I.WorkflowInitiator,

I.WorkflowID,

I.State,

CONVERT(VARCHAR(24),MAX(P.TimeStamp),120) as LastActionDate,

COUNT(P.WorkflowProgressID) as ActionCount

FROM WorkflowInstance I INNER JOIN

WorkflowProgress P ON I.InstanceID = P.InstanceID

GROUP BY I.WorkflowName, I.WorkflowInstanceID, CONVERT(VARCHAR(24), I.StartTime, 120), I.SiteID, I.WebID, I.ListID, I.ItemID, I.WorkflowInitiator, I.WorkflowID, I.State

ORDER BY I.SiteID, I.WebID, I.ListID ASC

Write-Progress -Activity "Getting WorkflowProgress Data" -Status "Running SQL Query" -PercentComplete 100

$data = Run-SQLQuery -SqlServer $DBServer -SqlDatabase $DBName -SqlQuery $query

$siteUrl = ""

$webUrl = ""

$listTitle = ""

$DataArray=@()

$SiteArray = @{}

$WebArray = @{}

$ListArray = @{}

if ($data.Count -gt 0)

{

if ((Get-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue) -eq $null)

{

Add-PSSnapin Microsoft.SharePoint.PowerShell

}

$i = 0

$nbrRows = $data.Count

$percComplete = 0

$percRow = [Math]::Floor(100 / $nbrRows)

foreach ($row in $data)

{

$i++

$percComplete += $percRow

Write-Progress -Activity "Processing Data" -Status "Record $i/$nbrRows" -PercentComplete $percComplete

if ($row.SiteID -ne $null)

{

# Check if we already processed this site before

if ($SiteArray.ContainsKey($row.SiteID))

{

$siteUrl = $SiteArray.Get_Item($row.SiteID)

}

else

{

$site = Get-SPSite $row.SiteID -ea SilentlyContinue

if ($site -eq $null)

{

$siteUrl = "$($row.SiteID) (DELETED)"

}

else

{

$siteUrl = $site.Url

}

$SiteArray.Set_Item($row.SiteID,$siteUrl)

}

# Check if we already processed this web before

if ($WebArray.ContainsKey($row.WebID))

{

$webUrl = $WebArray.Get_Item($row.WebID)

}

else

{

if ($site -eq $null)

{

$webUrl = "$($row.WebID) (DELETED)"

}

else

{

$web = $site.AllWebs | ? {$_.Id -eq $row.WebID}

if ($web -eq $null)

{

$webUrl = "$($row.WebID) (DELETED)"

}

else

{

$webUrl = $web.Url

}

$WebArray.Set_Item($row.WebID,$webUrl)

}

# Check if we already processed this list before

if ($row.ListID -ne "00000000-0000-0000-0000-000000000000")

{

if ($ListArray.ContainsKey($row.ListID))

{

$listTitle = $ListArray.Get_Item($row.ListID)

}

else

{

if ($web -eq $null)

{

$listTitle = "$($row.ListID) (DELETED)"

}

else

{

$list = $web.Lists | ? {$_.Id -eq $row.ListID}

if ($list -eq $null)

{

$listTitle = "$($row.ListID) (DELETED)"

}

else

{

$listTitle = $list.Title

}

$ListArray.Set_Item($row.ListID,$listTitle)

}

if ($web -ne $null)

{

$web.Dispose()

}

if ($site -ne $null)

{

$site.Dispose()

}

$state = ""

switch ($row.State)

{

2 { $state = "Running" }

4 { $state = "Completed" }

8 { $state = "Cancelled" }

64 { $state = "Errored" }

}

$item = @{

"WorkflowName" = $row.WorkflowName

"WorkflowInstanceID" = $row.WorkflowInstanceID

"StartTime" = $row.StartTime

"Site" = $siteUrl

"Web" = $webUrl

"List" = $listTitle

"ItemID" = $row.ItemID

"WorkflowInitiator" = $row.WorkflowInitiator

"WorkflowID" = $row.WorkflowID

"State" = $state

"LastActionDate" = $row.LastActionDate

"ActionCount" = $row.ActionCount

}

$DataRow = New-Object PSObject -Property $item

$DataArray += $DataRow

}

$DataArray | Export-Csv -NoTypeInformation -Path wflProgress.csv -Delimiter ";"

}

This not only gives me information I can work with immediately, but it also allows me to do it all from my SharePoint server. I don’t need a SQL Management studio anymore or physical access to the database server.

Depending on the amount of rows that is returned from SQL, the execution of the script can take some time though. I tried to minimize the calls to SharePoint by using arrays where I store the URL’s and titles for found GUID’s. This reduces the stress on SharePoint a bit but it still requires some time to go through each row.

Using this list, you can prioritize the things to focus on during a cleanup. And it gives you also the ability to predict the impact of a purge on that table using specific parameters.

Following sites not working in a Hybrid Sites scenario – 401 Unauthorized

Friday, 3 February, 2017Bart

Earlier this week, I was at one of my customers which has a SharePoint 2013 implementation. They had an issue where following sites was not working anymore. When they clicked the Follow link, they got an error that the site could not be followed.

They have a hybrid implementation with OneDrive for Business and Hybrid Sites setup.
When I looked in the ULS, I saw the following error popping up

FollowedContent.FollowItem:Exception:System.Net.WebException: The remote server returned an error: (401) Unauthorized.    
 at System.Net.HttpWebRequest.GetResponse()    
 at Microsoft.SharePoint.Client.SPWebRequestExecutor.Execute()    
 at Microsoft.SharePoint.Client.ClientRequest.ExecuteQueryToServer(ChunkStringBuilder sb)    
 at Microsoft.Office.Server.UserProfiles.FollowedContentProxy.Execute(String methodName)    
 at Microsoft.Office.Server.UserProfiles.FollowedContent.DoHybridFollow(String scopeName, FollowedItem item)    
 at Microsoft.Office.Server.UserProfiles.FollowedContent.FollowItem(FollowedItem item, Boolean isInternal)

FollowedContent.FollowItem:Exception:System.Net.WebException: The remote server returned an error: (401) Unauthorized.

at System.Net.HttpWebRequest.GetResponse()

at Microsoft.SharePoint.Client.SPWebRequestExecutor.Execute()

at Microsoft.SharePoint.Client.ClientRequest.ExecuteQueryToServer(ChunkStringBuilder sb)

at Microsoft.Office.Server.UserProfiles.FollowedContentProxy.Execute(String methodName)

at Microsoft.Office.Server.UserProfiles.FollowedContent.DoHybridFollow(String scopeName, FollowedItem item)

at Microsoft.Office.Server.UserProfiles.FollowedContent.FollowItem(FollowedItem item, Boolean isInternal)

Loud and clear… authentication issues.

Microsoft has an excellent resource where they outline the roadmap to implement hybrid features.

Both roadmaps outline the steps which are needed to set up those features. Since OneDrive for Business was working fine, I focused on the Hybrid Sites features and started going through the steps of the roadmap to see if everything was set up correctly.

Configure Office 365 for SharePoint hybrid – Check!
Set up SharePoint services for hybrid environments – Check!
Install the September PU for SharePoint Server 2013 – We were on the December 2016 CU, so … Check!
Configure S2S authentication from SharePoint Server 2013 to SharePoint Online – Hmmm… I don’t recall doing this in the past.
Configure hybrid sites features in Central Administration – Check!

Since I was getting authentication issues, and I didn’t recall me doing the S2S authentication configuration step, I figured that this was the cause of the problem.

When you follow the link for that step, you will see that there’s some work to do to set it up. Luckily, Microsoft provided a tool which actually does it for you. It’s called the Hybrid Picker. This simplifies things a bit.

Convert a SharePoint 2016 front-end to a front-end with Distributed Cache

Monday, 14 November, 2016Bart

I have been running a SharePoint 2016 farm with 4 servers (Front-End, Application, Distributed Cache, Search) but now Feature Pack 1 has been released, I’m looking to reduce this to 2 servers and use the 2 combined minroles which have been added. To be more precise, I want to eliminate my Distributed Cache server and convert my Front-End to a Front-End with Distributed Cache. After that, I want to do the same for my Search server and convert my application server to an application server with search. But doing this for my Distributed Cache server, proved to be a challenge. I was getting all kind of errors. This might have something to do with the order I did things though.
Before I did the conversion, I removed my Distributed Cache server from the farm and I didn’t do this in a graceful way… I just disconnected it from the farm.

My first attempt to convert a server failed with a very self-explanatory error:

The Distributed Cache service instance must be configured. Run ‘Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache’ on this server to configure the Distributed Cache service instance.

minrole1

That’s pretty clear. This is indeed a front-end server and in SharePoint 2016, this role doesn’t have the Distributed Cache service instance. So, I added it like the error proposed.

Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache

1	Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache

The result however was not what I expected. I got the error:

Failed to connect to hosts in the cluster

minrole2

Come to think of it… this actually makes sense. I already mentioned I kicked out the Distributed Cache server in a very direct way. Probably, my cluster is still referencing this host. The only way to verify this is to look at the cluster configuration. To get this information, you can export it to a text file.

Export-CacheClusterConfig -Path c:\temp\appfabric.txt

1	Export-CacheClusterConfig -Path c:\temp\appfabric.txt

Somewhere near the end of the file a “hosts” section can be found and this contained the following information:

minrole2a

And this confirmed it. The old host was still listed in the configuration. I had to remove this host first. To do this, I executed the following command:

Unregister-CacheHost -HostName bkuctg-sp133.evilvjeken.com -ProviderType SPDistributedCacheClusterProvider -ConnectionString \\bkuctg-sp133.evilvjeken.com

1	Unregister-CacheHost -HostName bkuctg-sp133.evilvjeken.com -ProviderType SPDistributedCacheClusterProvider -ConnectionString \\bkuctg-sp133.evilvjeken.com

This succesfully unregistered my host from the cluster and I re-ran the command to add the Distributed Cache Service Instance.

Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache

1	Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache

This time, no error! Hooray!

I proceeded to run the Set-SPServer command again to start the conversion. Again, an error appeared. This time, the error was different.

The Distributed Cache service instance must be reconfigured. Run ‘Remove-SPDistributedCacheServiceInstance’ on this server to remove the Distributed Cache service instance. Then run ‘Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache’ on this server to reconfigure the Distributed Cache service instance….

minrole3

At this point, when you look at the Distributed Cache service in Central Administration, you can see that it’s stopped. If you open the Windows Services console on the server, the AppFabric Caching Service is disabled.

I tried to remove this service using Remove-SPDistributedCacheServiceInstance, but it failed with the error:

cacheHostInfo is null

minrole4

To get past this point and get the service removed, you can execute the following piece of PowerShell script:

$farm = Get-SPFarm
$cacheClusterName = “SPDistributedCacheCluster_” + $farm.ID.ToString()
$cacheClusterManager = [Microsoft.SharePoint.DistributedCaching.Utilities.SPDistributedCacheClusterInfoManager]::Local
$cacheClusterInfo = $cacheClusterInfoManager.GetSPDistributedCacheClusterInfo($cacheClusterName)
$instanceName ="SPDistributedCacheService Name=AppFabricCachingService"
$serviceInstance = Get-SPServiceInstance | ? {($_.Service.Tostring()) -eq $instanceName -and ($_.Server.Name) -eq $env:COMPUTERNAME}
$serviceInstance.Delete()

$farm = Get-SPFarm

$cacheClusterName = “SPDistributedCacheCluster_” + $farm.ID.ToString()

$cacheClusterManager = [Microsoft.SharePoint.DistributedCaching.Utilities.SPDistributedCacheClusterInfoManager]::Local

$cacheClusterInfo = $cacheClusterInfoManager.GetSPDistributedCacheClusterInfo($cacheClusterName)

$instanceName ="SPDistributedCacheService Name=AppFabricCachingService"

$serviceInstance = Get-SPServiceInstance | ? {($_.Service.Tostring()) -eq $instanceName -and ($_.Server.Name) -eq $env:COMPUTERNAME}

$serviceInstance.Delete()

This worked and it removed the service instance completely from my server.

I then proceeded to execute the Add-SPDistributedCacheServiceInstance command again but this time without the -Role parameter!!!

When the command finished, I went to the Services on Server page in Central Administration and I noticed that the Distributed Cache service was added again. This time it was started! It also stated that my server was not compliant anymore but who cares…. we are going to change the role anyway.

minrole5

I re-ran the Set-SPServer command to start the conversion:

Set-SPServer -Identity bkuctg-sp131 -Role WebFrontEndWithDistributedCache

1	Set-SPServer -Identity bkuctg-sp131 -Role WebFrontEndWithDistributedCache

And yes, now it worked. I was notified that a timer job had been scheduled to do the conversion.

minrole6

I only had to wait until the job was completed. When I looked at the Servers page in Central Administration, I could clearly see that my role was changed and that the server was compliant.

minrole7

Part of this could possibly have been avoided by removing the Distributed Cache server in a more graceful way. Lessons learned… don’t cut corners! 😎

DistributedCache Server role vs SkipRegisterAsDistributedCacheHost

Thursday, 10 November, 2016Bart

Since SharePoint 2013, the New-SPConfigurationDatabase and Connect-SPConfigurationDatabase cmdlets have a parameter called “SkipRegisterAsDistributedCacheHost”. When this switch parameter is specified during the creation of a new farm or when a server is added to an existing farm, the local server will not have the Distributed Cache. With the arrival of SharePoint 2016, we also got the MinRole feature. This feature enables you to designate the local server to be a “DistributedCache” server. How to do this, is explained in one of my previous posts where I provide a script to create a farm and to join a farm. I was wondering what happens when you use the DistributedCache server role together with the -SkipRegisterAsDistributedCacheHost switch.

Best way of finding this out is to try it. I did, and found out that the server in fact was a DistributedCache host after I joined it to the farm. So, it ignores the switch completely.

But what with the other roles? How about Custom? And Search? To see how this is handled, I fired up ILSpy and started digging in the code.

In the Microsoft.SharePoint.DistributedCaching.Utilities.SPDistributedCacheServiceInstance class, a method ShouldRegisterAsDistributedCacheHost exists and this is called somewhere during the execution of one of the above 2 cmdlets.

internal static bool ShouldRegisterAsDistributedCacheHost(bool skipRegisterAsDistributedCacheHost, SPServerRole? serverRole)
{
    if (SPServerRoleManager.IsMinRoleFeatureEnabled())
    {
        return serverRole == SPServerRole.DistributedCache || serverRole == SPServerRole.SingleServerFarm || serverRole == SPServerRole.WebFrontEndWithDistributedCache || ((serverRole == SPServerRole.Custom || !serverRole.HasValue) && !skipRegisterAsDistributedCacheHost);
    }
    return !skipRegisterAsDistributedCacheHost;
}

internal static bool ShouldRegisterAsDistributedCacheHost(bool skipRegisterAsDistributedCacheHost, SPServerRole? serverRole)

{

if (SPServerRoleManager.IsMinRoleFeatureEnabled())

{

return serverRole == SPServerRole.DistributedCache || serverRole == SPServerRole.SingleServerFarm || serverRole == SPServerRole.WebFrontEndWithDistributedCache || ((serverRole == SPServerRole.Custom || !serverRole.HasValue) && !skipRegisterAsDistributedCacheHost);

}

return !skipRegisterAsDistributedCacheHost;

}

This method contains a test which illustrates how the decision is made if a server should be a DistributedCache host or not.

Bottomline…

If the ServerRole parameter is DistributedCache, SingleServerFarm or WebFrontEndWithDistributedCache, the presence of the SkipRegisterAsDistributedCacheHost doesn’t matter. The server will be a Distributed Cache host. Period.

If the ServerRole parameter is not specified, “Custom” or we don’t have the MinRole feature enabled, the SkipRegisterAsDistributedCacheHost is taken into account and used in the decision.

Change SharePoint Service Identities using PowerShell

Saturday, 1 October, 2016Wednesday, 22 March, 2017Bart

After installing SharePoint and setting up my farm, one of the first things I always do is change SharePoint service identities. In a freshly installed SharePoint farm, most services are running under the farm account or under a local identity (LocalService, LocalSystem). Some of the services I change right away:

Search Host Controller Service
SharePoint Server Search
Distributed Cache
SharePoint Tracing Service

With the exception of the SharePoint Tracing Service, all of these identities can be changed from the “Service Accounts” page in Central Administration. But where’s the fun in that… furthermore, this page has one big disadvantage. You can change a service to run with a managed account but you can’t set it to run under a local account (LocalService, LocalSystem, NetworkService). So, if you changed your service from a local account to a domain account, you can’t undo this change using the UI. You need to use PowerShell.

The script below allows you to set a domain account or a local account.

<#
.SYNOPSIS
   Specify a new service identity for a SharePoint Service.
	
.DESCRIPTION
   Specify a new service identity for a SharePoint Service.
	
.NOTES
   File Name: Set-ServiceIdentityForSPService.ps1
   Author   : Bart Kuppens
   Version  : 1.2
	
.PARAMETER ServiceName
   Specifies the name of the SharePoint service.

.PARAMETER TypeName
   Specifies the typename of the SharePoint service.

.PARAMETER LocalIdentity
   If specified, will assign a local identity to the service.

.PARAMETER DomainIdentity
   If specified, will assign a domain account to the service.

.PARAMETER Identity
   Specifies the name of the local identity (LocalService, LocalSystem or NetworkService).

.PARAMETER AccountName
   Specifies the name of a managed account to use as identity.

.EXAMPLE
   PS > .\Set-ServiceIdentityForSPService.ps1 -ServiceName "c2wts" -LocalIdentity -Identity LocalService

   DESCRIPTION
   -----------
   Will change the identity of the c2wts service to LocalService

.EXAMPLE
   PS > .\Set-ServiceIdentityForSPService.ps1 -ServiceName "c2wts" -DomainIdentity -AccountName "westeros\svc_spservice"

   DESCRIPTION
   -----------
   Will change the identity of the c2wts service to westeros\svc_spservice
#>
[CmdletBinding()]
param(
   [parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateNotNullOrEmpty()]
   [string]$ServiceName,
   [parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateNotNullOrEmpty()]
   [string]$TypeName,
   [parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateNotNullOrEmpty()]
   [switch]$LocalIdentity,
   [parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateNotNullOrEmpty()]
   [switch]$DomainIdentity,
   [parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateSet("LocalSystem","LocalService","NetworkService")]
   [string]$Identity,
   [parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]
   [ValidateNotNullOrEmpty()]
   [string]$AccountName
)

# Load the SharePoint PowerShell snapin if needed
if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
   Write-Host "Loading the SharePoint PowerShell snapin..."
   Add-PSSnapin Microsoft.SharePoint.PowerShell
}	

# Get the service
if ($ServiceName -eq $null)
{
   # Using the Type
   $svc = (Get-SPFarm).Services | where {$_.TypeName -eq $TypeName}
   if ($svc -eq $null)
   {
      Write-Host "The service with the type '$TypeName' doesn't exist. Halting execution!"
      break
   }
}
else
{
   # Using the Name
   $svc = (Get-SPFarm).Services | where {$_.Name -eq $ServiceName}
   if ($svc -eq $null)
   {
      Write-Host "The service with the name '$ServiceName' doesn't exist. Halting execution!"
      break
   } 
}

# Get the managed account if required
if ($DomainIdentity)
{
   $managedAccount = Get-SPManagedAccount -Identity $AccountName
   if ($managedAccount -eq $null)
   {
      Write-Host "The domain account '$AccountName' is not a valid managed account. Halting execution!"
      break
   }
}

# Set the Service to run with a new identity
if ($LocalIdentity)
{
   $procId = $svc.ProcessIdentity
   $procId.CurrentIdentityType = $Identity
}
else
{
   if ($svc.TypeName -eq "SharePoint Server Search")
   {
      $procId = (Get-SPEnterpriseSearchService).get_ProcessIdentity()
   }
   else
   {
      $procId = $svc.ProcessIdentity
   }
   $procId.CurrentIdentityType = "SpecificUser"
   $procId.ManagedAccount = $managedAccount 
}
$procId.Update()
$procId.Deploy()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

.SYNOPSIS

Specify a new service identity for a SharePoint Service.

.DESCRIPTION

Specify a new service identity for a SharePoint Service.

.NOTES

File Name: Set-ServiceIdentityForSPService.ps1

Author : Bart Kuppens

Version : 1.2

.PARAMETER ServiceName

Specifies the name of the SharePoint service.

.PARAMETER TypeName

Specifies the typename of the SharePoint service.

.PARAMETER LocalIdentity

If specified, will assign a local identity to the service.

.PARAMETER DomainIdentity

If specified, will assign a domain account to the service.

.PARAMETER Identity

Specifies the name of the local identity (LocalService, LocalSystem or NetworkService).

.PARAMETER AccountName

Specifies the name of a managed account to use as identity.

.EXAMPLE

PS > .\Set-ServiceIdentityForSPService.ps1 -ServiceName "c2wts" -LocalIdentity -Identity LocalService

DESCRIPTION

-----------

Will change the identity of the c2wts service to LocalService

.EXAMPLE

PS > .\Set-ServiceIdentityForSPService.ps1 -ServiceName "c2wts" -DomainIdentity -AccountName "westeros\svc_spservice"

DESCRIPTION

-----------

Will change the identity of the c2wts service to westeros\svc_spservice

[CmdletBinding()]

param(

[parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateNotNullOrEmpty()]

[string]$ServiceName,

[parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateNotNullOrEmpty()]

[string]$TypeName,

[parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateNotNullOrEmpty()]

[switch]$LocalIdentity,

[parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateNotNullOrEmpty()]

[switch]$DomainIdentity,

[parameter(ParameterSetName="ServiceNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="TypeNameLocal",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateSet("LocalSystem","LocalService","NetworkService")]

[string]$Identity,

[parameter(ParameterSetName="ServiceNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[parameter(ParameterSetName="TypeNameDomain",Mandatory=$true,ValueFromPipeline=$false)]

[ValidateNotNullOrEmpty()]

[string]$AccountName

)

# Load the SharePoint PowerShell snapin if needed

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)

{

Write-Host "Loading the SharePoint PowerShell snapin..."

Add-PSSnapin Microsoft.SharePoint.PowerShell

}

# Get the service

if ($ServiceName -eq $null)

{

# Using the Type

$svc = (Get-SPFarm).Services | where {$_.TypeName -eq $TypeName}

if ($svc -eq $null)

{

Write-Host "The service with the type '$TypeName' doesn't exist. Halting execution!"

break

}

else

{

# Using the Name

$svc = (Get-SPFarm).Services | where {$_.Name -eq $ServiceName}

if ($svc -eq $null)

{

Write-Host "The service with the name '$ServiceName' doesn't exist. Halting execution!"

break

}

# Get the managed account if required

if ($DomainIdentity)

{

$managedAccount = Get-SPManagedAccount -Identity $AccountName

if ($managedAccount -eq $null)

{

Write-Host "The domain account '$AccountName' is not a valid managed account. Halting execution!"

break

}

# Set the Service to run with a new identity

if ($LocalIdentity)

{

$procId = $svc.ProcessIdentity

$procId.CurrentIdentityType = $Identity

}

else

{

if ($svc.TypeName -eq "SharePoint Server Search")

{

$procId = (Get-SPEnterpriseSearchService).get_ProcessIdentity()

}

else

{

$procId = $svc.ProcessIdentity

}

$procId.CurrentIdentityType = "SpecificUser"

$procId.ManagedAccount = $managedAccount

}

$procId.Update()

$procId.Deploy()

Cleaning up obsolete SharePoint groups

Wednesday, 28 September, 2016Bart

During the lifetime of a SharePoint implementation, sites come and go. When a site collection grows, you typically see the amount of SharePoint groups growing as well because you want to give people access to those sites in a sort of organized way. When sites go, those groups are left behind. Removing those obsolete SharePoint groups can be a challenging task because groups which have been created for a specific site can be used for other sites as well. So, before removing a group, you need to be sure that it’s not used on any other sub sites.

SharePoint groups live on the root web of a site collection.

If a group is created as part of the site creation process, it will have a description which clearly states for which site is has been created. This doesn’t mean it can’t be used on any other sites. If you want to get a list of all SharePoint groups which exist in a site collection, you can also use the following PowerShell snippet to get the collection.

$site = Get-SPSite <SiteColURL>
$site.RootWeb.SiteGroups | Select Name, Description

1 2	$site = Get-SPSite <SiteColURL> $site.RootWeb.SiteGroups \| Select Name, Description

obsolete-sharepoint-groups-1

If you want to know which groups are used on a specific subsite of the site collection, you can use the UI and check the Site Permissions section of a site. This will give you all permissions for that site. You can also use the following PowerShell snippet to get these.

$web = $site.RootWeb
$web.Groups | Select Name, Roles

1 2	$web = $site.RootWeb $web.Groups \| Select Name, Roles

obsolete-sharepoint-groups-2

See the difference? The SiteGroups has a group “MyCustomGroup” which is not part of the Groups collection of the same web. This means that the group exists in the site collection but at this point, it’s not used. When I give this group explicit permissions to my site, it will be added to this collection and it will be in use.

So, the process of cleaning up obsolete groups is to check the Groups collection on each sub site and see which site groups are used for giving people access. If you have a site group which is not part of any Groups collection of any site, it’s not used and you can remove that group from the site collection.

You can do this manually, or you can automate this process and use the following script for this task.

This script will do 2 things.
If run in Simulation mode, it will look for obsolete groups and ouput them to the console. Nothing more.
If run in Execution mode, it will look for obsolete groups and delete them from the site collection.

<#
.SYNOPSIS
    Identify unused SharePoint groups and delete them
	
.DESCRIPTION
    Identify unused SharePoint groups and delete them.
	
.NOTES
    File Name: Delete-UnusedGroups.ps1
    Author   : Bart Kuppens
    Version  : 1.0
	
.PARAMETER Site
    Specifies the URL of the site collection to cleanup.

.PARAMETER ViewOnly
    If included, obsolete groups are outputted to the screen and NOT deleted (SIMULATION mode).
    If omitted, obsolete groups are deleted (EXECUTION mode).

.EXAMPLE
    PS > .\Get-UnusedGroups.ps1 -Site http://teamsites.westeros.local -ViewOnly
#>
[CmdletBinding()]
param(
    [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Specifies the URL of the site collection to cleanup.")] 
    [string]$Site,
    [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$false,HelpMessage="If true, obsolete groups are outputted to the screen and NOT deleted.")] 
    [switch]$ViewOnly
)
cls
if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
{ 
    Write-Host "Loading SharePoint cmdlets..."
    Add-PsSnapin Microsoft.SharePoint.PowerShell
}

$SPSite = Get-SPSite $site
if ($SPSite -eq $null)
{
    Write-Host "There's no site at URL $site. Halting execution!"
    break
}

if ($ViewOnly)
{
    Write-Host -ForegroundColor Red -BackgroundColor Yellow "Running in SIMULATION mode!!!"
}
else
{
    Write-Host -ForegroundColor Red -BackgroundColor Yellow "Running in EXECUTION mode!!!"
}

$groups = $SPSite.RootWeb.SiteGroups
$groupsToDelete = @()

$nbrGroups = $groups.Count
$percGroups = 100 / $nbrGroups
$compGroups = 0
$nbrObsoleteGroups = 0

foreach ($group in $groups)
{
    $hasAccess = $false
    $compGroups += $percGroups
    Write-Progress -Activity "Checking obsolete groups ($nbrObsoleteGroups/$nbrGroups found)" -Status $group.Name -PercentComplete $compGroups
    # Check if this group has been given access to a subsite
    $webs = $SPSite.AllWebs
    if ($webs.Count -gt 0)
    {
        foreach ($web in $webs)
        {
            if ($web.Groups["$group"] -ne $null)
            {
                $hasAccess = $true
            }
            $web.Dispose()
        }
    }
    if ($hasAccess -eq $false)
    {
        $groupsToDelete += $group
        $nbrObsoleteGroups++
    }
}

if ($ViewOnly)
{
    Write-Host "$($groupsToDelete.Count) groups were found."
    if ($nbrObsoleteGroups -gt 0)
    {
        Write-Host "Following groups would be removed in DELETE mode"
        $groupsToDelete | Select Name
    }
}
else
{
    $nbrGroups = $groupsToDelete.Count
    $percGroups = 100 / $nbrGroups
    $compGroups = 0
    $i = 1
    foreach ($item in $groupsToDelete)
    {
        $compGroups += $percGroups
        Write-Progress -Activity "Deleting group $i/$nbrGroups" -Status $item.Name -PercentComplete $compGroups
        $SPSite.RootWeb.SiteGroups.Remove($item)
        $i++
    }
}
$SPSite.Dispose()

100

101

102

103

104

105

106

107

108

109

.SYNOPSIS

Identify unused SharePoint groups and delete them

.DESCRIPTION

Identify unused SharePoint groups and delete them.

.NOTES

File Name: Delete-UnusedGroups.ps1

Author : Bart Kuppens

Version : 1.0

.PARAMETER Site

Specifies the URL of the site collection to cleanup.

.PARAMETER ViewOnly

If included, obsolete groups are outputted to the screen and NOT deleted (SIMULATION mode).

If omitted, obsolete groups are deleted (EXECUTION mode).

.EXAMPLE

PS > .\Get-UnusedGroups.ps1 -Site http://teamsites.westeros.local -ViewOnly

[CmdletBinding()]

param(

[Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false,HelpMessage="Specifies the URL of the site collection to cleanup.")]

[string]$Site,

[Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$false,HelpMessage="If true, obsolete groups are outputted to the screen and NOT deleted.")]

[switch]$ViewOnly

)

cls

if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )

{

Write-Host "Loading SharePoint cmdlets..."

Add-PsSnapin Microsoft.SharePoint.PowerShell

}

$SPSite = Get-SPSite $site

if ($SPSite -eq $null)

{

Write-Host "There's no site at URL $site. Halting execution!"

break

}

if ($ViewOnly)

{

Write-Host -ForegroundColor Red -BackgroundColor Yellow "Running in SIMULATION mode!!!"

}

else

{

Write-Host -ForegroundColor Red -BackgroundColor Yellow "Running in EXECUTION mode!!!"

}

$groups = $SPSite.RootWeb.SiteGroups

$groupsToDelete = @()

$nbrGroups = $groups.Count

$percGroups = 100 / $nbrGroups

$compGroups = 0

$nbrObsoleteGroups = 0

foreach ($group in $groups)

{

$hasAccess = $false

$compGroups += $percGroups

Write-Progress -Activity "Checking obsolete groups ($nbrObsoleteGroups/$nbrGroups found)" -Status $group.Name -PercentComplete $compGroups

# Check if this group has been given access to a subsite

$webs = $SPSite.AllWebs

if ($webs.Count -gt 0)

{

foreach ($web in $webs)

{

if ($web.Groups["$group"] -ne $null)

{

$hasAccess = $true

}

$web.Dispose()

}

if ($hasAccess -eq $false)

{

$groupsToDelete += $group

$nbrObsoleteGroups++

}

if ($ViewOnly)

{

Write-Host "$($groupsToDelete.Count) groups were found."

if ($nbrObsoleteGroups -gt 0)

{

Write-Host "Following groups would be removed in DELETE mode"

$groupsToDelete | Select Name

}

else

{

$nbrGroups = $groupsToDelete.Count

$percGroups = 100 / $nbrGroups

$compGroups = 0

$i = 1

foreach ($item in $groupsToDelete)

{

$compGroups += $percGroups

Write-Progress -Activity "Deleting group $i/$nbrGroups" -Status $item.Name -PercentComplete $compGroups

$SPSite.RootWeb.SiteGroups.Remove($item)

$i++

}

$SPSite.Dispose()

My advice… run it in Simulation mode before running it in Execution mode. That way, you have an idea of what groups were found and will be deleted.

There are some situations which need clarifications.

Inherited permissions

What happens when you have subsites which inherit permissions? This is no issue. Suppose you have a subsite which inherits permissions of the root web of the site collection. When a SharePoint group is given access to the root web of the site collection, it will be given access to all sub sites which inherit their permissions and as such, that group will be part of the Groups collection of those sub sites.

Audience targeting

What happens when a group is exclusively used for audience targeting? Well, this is a problem because that group is not part of the Groups collection of the site where you have used it as an audience. In my opinion, this is a situation you should avoid doing because you are going to give a collection of users access to a site. In theory, an audience is a subset of authorized users, right? You want to target specific content on a site to specific users. If they can’t reach the site, what’s the point in targeting content to them?

If you do find yourself in such a situation where you have SharePoint groups which are exclusively used for audience targeting, a good approach would be to give those groups distinctive names, clearly indicating they are audience targeting groups. For example, you could start each group name with “AUD_”. This way, you can extend the script above and include a check to skip groups which start with “_AUD”.

SharePoint issues when using a trust with Selective Authentication

Monday, 12 September, 2016Wednesday, 14 September, 2016Bart

If you have some experience with SharePoint, the issue where you get a credential request three times before hitting the 401 Unauthorized is probably not new to you. We all know this happens when you try to navigate to a SharePoint site from the web front-end servers. Resolving this is common knowledge for SharePoint admins… You disable the loopback check in the registry or you use the recommended BackConnectionHostNames registry key. This has been documented in KB896861.

Last week, I was at a customer doing an assessment of a SharePoint implementation and one of their developers approached me with a weird issue on their Extranet. They have a SharePoint farm in a separate extranet domain. Between the internal domain and the extranet domain is a one-way trust to allow users from the internal domain to use their accounts to log on to a site on the Extranet. He was able to do this from the web front-end servers of the Extranet farm but not from his laptop. On his laptop, he had to enter his credentials and this kept failing… seems familiar right?

I double-checked the BackConnectionHostNames on the servers and sure enough, the key and hosts were there.

I tried the same thing on my machine with my account and this worked! I was able to go to the site from my machine. When he tried to do it from my machine with his account, it failed. We tested this on several other clients with several users… ALL of them had the same issue. Nobody was able to sign-in. Only I was able to sign-in from any place.

I will spare you the checks and comparisons we did, but I will tell you that we were able to solve it!

Servers in a domain are, like user accounts, just objects in Active Directory. When you open the properties of such a computer object in AD, and you go to the Security tab, you can specify a lot of permissions which specific AD objects can have on this computer. One of those permissions is “Allowed to authenticate”. For the servers in that Extranet farm, I was explicitly granted that permission, while the “Authenticated Users” group was not…

allowed-to-authenticate

In normal circumstances, this doesn’t pose any issue. If you have 1 domain which contains your users and servers, this permission is not required. Furthermore, if you have multiple domains and a one-way trust and you keep the default trust authentication level (Forest-wide authentication), you will not have any issues with users from the trusted domain authenticating to resources in the trusting domain.

selective-authentication-02

However, when you are using “Selective Authentication”, you need to explicitly grant the “Allowed to authenticate” permission to all users on the resources they need to access. When we verified this authentication level at the customer, we got confirmation that they were using selective authentication. So, we had to give “Authenticated Users” this permission on the SharePoint servers in the AD of the Extranet to resolve this issue.

See following articles for more information on selective authentication on trusts.

The SharePoint 2013 installer doesn’t like .NET Framework 4.6.x

Monday, 6 June, 2016Wednesday, 14 September, 2016Bart

One of the Prerequisites of SharePoint 2013 is the .NET Framework 4.5. I have been installing countless SharePoint 2013 environments without issues but recently, I started noticing that the SharePoint installer fails with the following error :

Setup is unable to proceed due to the following error(s):
This product requires Microsoft .NET Framework 4.5.

NET Framework 4.6 - pic 2

This might seem strange since the prerequisite installer installed everything. When this happens, you might want to have a look at the actual installed versions of the .NET Framework. You will probably notice that .NET Framework 4.6 or higher is installed. To check which versions have been installed, you can download the .NET Framework Setup Verification Utility from Microsoft to verify which versions of the .NET Framework have been installed. When you run this tool, you will see an overview of all installed versions.

NET Framework 4.6 - pic2

When you see the .NET Framework 4.6 or 4.6.1 listed, the SharePoint installer cannot detect the presence of version 4.5.

With the release of Service Pack 1 for SharePoint 2013, SharePoint is compatible with version 4.6 but the installer isn’t. You can have version 4.6, but only AFTER you installed SharePoint 2013.

So, in order to get SharePoint installed on that machine, you need to uninstall version 4.6 or higher before you can install SharePoint 2013. And this is where it gets a bit annoying. The tool to verify the .NET Framework also has the ability to uninstall a version. Except… this doesn’t work! You can try it but nothing happens. When you recheck, it’s still there. Even after a reboot.

This version of the .NET Framework can be uninstalled by uninstalling Windows update KB3102467. But this is just the first action… if you are installing SharePoint in an organization which pushes Windows Updates, you probably want to block this framework from installing on that machine for the time being.

To do that, you can execute the following PowerShell script. This is going to add a key ‘BlockNetFramework461’ to the registry and set it to 1 to block the installation of that version. Once SharePoint is installed, you can remove this key if you want.

$Path = 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\WU'
$Property = 'BlockNetFramework461'

$KeyExists = Test-Path $Path
if (!$KeyExists)
{
    New-Item -Path $Path
}
$PropExists = Get-ItemProperty -Path $Path -Name $Property -EA SilentlyContinue
if (!$PropExists)
{
    New-ItemProperty -Path $Path -Name $Property -PropertyType Dword -Value 1
}

$Path = 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\WU'

$Property = 'BlockNetFramework461'

$KeyExists = Test-Path $Path

if (!$KeyExists)

{

New-Item -Path $Path

}

$PropExists = Get-ItemProperty -Path $Path -Name $Property -EA SilentlyContinue

if (!$PropExists)

{

New-ItemProperty -Path $Path -Name $Property -PropertyType Dword -Value 1

}

To see the official article for the blocking of the installation of the .NET Framework 4.6.1, see the following link:

https://support.microsoft.com/en-us/kb/3133990

Once that version is uninstalled, you can proceed with the installation of SharePoint 2013 on that machine.

Update (14/09/2016)

Microsoft released a fix for the installer issue. You can find this fix in KB3087184.

Cleaning up large content databases and reclaiming unused disk space

Friday, 27 May, 2016Sunday, 29 May, 2016Bart

Dealing with large SharePoint content databases can be a daunting task. Microsoft has the recommendation to keep your content databases below the 200GB mark for databases which are used daily. But when you are dealing with backup/restore, migrations and general operations which involve moving around those kind of databases, even 200GB can be a huge pain in the ass and will cost you in terms of time you are spending looking at a progress bar or watching a percentage creeping slowly to 100%.

A solution for this is to split up the content database into smaller databases provided that you have multiple site collections in that database which can be moved out.
Relocating a site collection to a different content database is very easy. You can do this with the Move-SPSite cmdlet.

Once you moved out a site collection to a different database, you will notice that your huge database is not getting smaller. That’s because the site collection which has been moved, is still in that database but it’s marked for deletion.
The actual removal of that site collection is done by the Gradual Site Delete timer job. This job runs once a day. Once it has run, the site collection is completely removed from the database.
But still, if you look at the database, it will not be any smaller than before. When you look at the used space in the database, you will see that this has decreased and the unused space has grown. The unused space is not released.
To release unused space, … *ducks for cover* … you can shrink the database. There, I said it.

Generally speaking, shrinking content databases is not done. You can do this, but it has no advantages and it has a negative effect on performance because when someone adds something to a site in that database, it has to expand to be able to store anything.
So, shrinking is definitely something you should avoid at any cost… except for the case where you have such a huge content database that you’ve split up into smaller content databases. The reason for splitting up the database in the first place was to make it smaller, right? To have a size which is much more manageable. But in order to get it back to a reasonable size, you need to shrink it. There’s no way around it.

During a migration from SharePoint 2007 to SharePoint 2013, I had to migrate a content database of 220GB. All things considered, this is not huge. It’s large, but not huge. This content database contained around 20 site collections. Backup of this database was not an issue… 20 minutes to backup this database. Copying this backup to a SharePoint 2010 migration server was frustrating. It took over an hour. Yeah, It SHOULD go faster but if you pass through a 10Mbit router, you are copying at USB 2.0 speed! But this was nothing compared to the time the Mount-SPContentDatabase cmdlet needed to complete to attach this database to the web application and do the actual upgrade from SP2007 to SP2010. This attach/upgrade took almost 3 hours and then it just aborted due to lack of disk space. The migration server had a data disk of 600GB and it just filled completely with the transaction log that was created as part of the attach/upgrade process. So, I lost 3 hours, had to wait until extra disk space was added and restart the whole thing again. By the time it had attached and upgraded, the size of the database was actually increased to 330GB.

When everything was attached and upgraded, I decided that I’m not going through this again when I do the migration from SP2010 to SP2013. I needed to have databases which are easier to handle. So, I split up this database into 5 databases of which the largest was still 115GB. But ok, nothing I could do about that in the short term.

Running the Gradual Site Delete job however proved to be a pain as well… took almost 6 hours to complete! Started around 2PM. Went home at 5PM. Next day, I noticed it finished around 8PM. So, I started the shrink operation of the database… lost half a day with that. Wasn’t able to do anything with that database for the larger part of the day.

Since this was only a “test” migration, I realised that history was going to repeat itself during the final migration and that I needed a way to make use of those lost hours between the finishing of the gradual site delete and the shrink. When the gradual site delete is done, start with the shrinking to have it done in the morning.

Enter… PowerShell!

The script below is going to kick off the Gradual Site Delete timer job for a specific web application and it then will monitor this job every 5 minutes to see if has completed. Once it has completed, it will continue with the shrinking of the database. The shrinking happens in 2 stages. The first stage is done with NOTRUNCATE. This means that SQL will move all pages to the front of the file but it will not reclaim unused space. The size stays the same. The next step is a shrink operation with TRUNCATEONLY. This will just remove all unused space from the end of the file. It’s basically the same thing that happens when you do a Shrink Database from the Management Studio.

Again, don’t do this as a part of a weekly maintenance routine because the first step of the shrink will introduce index fragmentation in your database. Seriously! For me, this was a necessary cleanup I had to do as part of a migration project to reorganize the content database and minimize the migration time! The environment I was doing this in, was a intermediate SharePoint 2010 environment, not a live environment.

Also, the Shrink operation in the script allows you to specify a percentage of free space it should reserve for unused space.

$db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)

1	$db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)

I used 5%. This way, for a content database of 100GB, 5GB of free space is retained. You can change this if you want, or you can add an additional parameter which allows you to specify the amount of free space it should keep.

<#
.SYNOPSIS
    Cleanup site collection marked for deletion and perform a shrink

.DESCRIPTION
    Cleanup site collection marked for deletion and perform a shrink.

.NOTES
    File Name: CleanUp-ContentDatabase.ps1
    Author   : Bart Kuppens
    Version  : 1.0
	
.PARAMETER WebApplication
    Specifies the URL of the webapplication where the cleanup has to be executed.
	
.PARAMETER ContentDatabase
    Specifies the name of the content database which has to be cleaned.
	
.EXAMPLE
    PS C:\> .\Cleanup-ContentDatabase.ps1 -WebApplication http://teamsites.westeros.local ContentDatabase "SHP_WST_Content_TeamSites"
#>
[CmdletBinding()]
param(
    [parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false)]
    [string]$WebApplication,
    [parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false)]
    [string]$ContentDatabase
)
cls
# Load the SharePoint PowerShell snapin if needed
if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
    Write-Output "Loading SharePoint Snap-in..."
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}

# Check if the webapplication exists
$webapp = Get-SPWebApplication $WebApplication -ErrorAction SilentlyContinue
if ($webapp -eq $null)
{
    Write-Host -ForegroundColor Red "Web application '$WebApplication' doesn't exist, halting execution!"
    break
}

# Check if the contentdatabase exists
$contentdb = Get-SPContentDatabase -Identity $ContentDatabase -ErrorAction SilentlyContinue
if ($contentdb -eq $null)
{
    Write-Host -ForegroundColor Red "Content database '$ContentDatabase' doesn't exist, halting execution!"
    break
}
else
{
    # Check if the content database is attached to the web application
    if ($contentdb.WebApplication -ne $webapp)
    {
        Write-Host -ForegroundColor Red "Content database '$ContentDatabase' is not attached to '$WebApplication', halting execution!"
        break
    }
}

# Start the Gradual Site Delete for the specified web application
$start = (Get-Date).ToUniversalTime()
$timerjob = Get-SPTimerJob -Identity "job-site-deletion" -WebApplication $webapp
Write-Host "Starting 'Gradual Site Delete' for $webApplication..."
Start-SPTimerJob -Identity $timerjob

# Wait for the job to complete
Write-Host -NoNewLine "Waiting for job completion on database $ContentDatabase..."
$jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}
while ($jobhistoryentries -eq $null)
{
    Start-Sleep -Seconds 300
    $jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}
}
Write-Host " Completed with status : $($jobhistoryentries.status)"
$jobhistoryentries

if ($jobhistoryentries.status -eq [Microsoft.SharePoint.Administration.SPRunningJobStatus]::Succeeded)
{
    Write-Host "Continuing with database Shrink..."
    [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") >> $null
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $contentdb.Server
    $db = $server.Databases[$ContentDatabase]
    Write-Host "Current size (Mb) : $($db.Size)"
    Write-Host "Shrinking Step 1/2..."
    $db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::NoTruncate)
    $db.Refresh()
    Write-Host "Shrinking Step 2/2..."
    $db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
    Write-Host "New size (Mb)     : $($db.Size)"
}
else
{
    Write-Host "The timer job failed. Halting execution!"
    break
}

.SYNOPSIS

Cleanup site collection marked for deletion and perform a shrink

.DESCRIPTION

Cleanup site collection marked for deletion and perform a shrink.

.NOTES

File Name: CleanUp-ContentDatabase.ps1

Author : Bart Kuppens

Version : 1.0

.PARAMETER WebApplication

Specifies the URL of the webapplication where the cleanup has to be executed.

.PARAMETER ContentDatabase

Specifies the name of the content database which has to be cleaned.

.EXAMPLE

PS C:\> .\Cleanup-ContentDatabase.ps1 -WebApplication http://teamsites.westeros.local ContentDatabase "SHP_WST_Content_TeamSites"

[CmdletBinding()]

param(

[parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false)]

[string]$WebApplication,

[parameter(Position=1,Mandatory=$true,ValueFromPipeline=$false)]

[string]$ContentDatabase

)

cls

# Load the SharePoint PowerShell snapin if needed

if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)

{

Write-Output "Loading SharePoint Snap-in..."

Add-PSSnapin Microsoft.SharePoint.PowerShell

}

# Check if the webapplication exists

$webapp = Get-SPWebApplication $WebApplication -ErrorAction SilentlyContinue

if ($webapp -eq $null)

{

Write-Host -ForegroundColor Red "Web application '$WebApplication' doesn't exist, halting execution!"

break

}

# Check if the contentdatabase exists

$contentdb = Get-SPContentDatabase -Identity $ContentDatabase -ErrorAction SilentlyContinue

if ($contentdb -eq $null)

{

Write-Host -ForegroundColor Red "Content database '$ContentDatabase' doesn't exist, halting execution!"

break

}

else

{

# Check if the content database is attached to the web application

if ($contentdb.WebApplication -ne $webapp)

{

Write-Host -ForegroundColor Red "Content database '$ContentDatabase' is not attached to '$WebApplication', halting execution!"

break

}

# Start the Gradual Site Delete for the specified web application

$start = (Get-Date).ToUniversalTime()

$timerjob = Get-SPTimerJob -Identity "job-site-deletion" -WebApplication $webapp

Write-Host "Starting 'Gradual Site Delete' for $webApplication..."

Start-SPTimerJob -Identity $timerjob

# Wait for the job to complete

Write-Host -NoNewLine "Waiting for job completion on database $ContentDatabase..."

$jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}

while ($jobhistoryentries -eq $null)

{

Start-Sleep -Seconds 300

$jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}

}

Write-Host " Completed with status : $($jobhistoryentries.status)"

$jobhistoryentries

if ($jobhistoryentries.status -eq [Microsoft.SharePoint.Administration.SPRunningJobStatus]::Succeeded)

{

Write-Host "Continuing with database Shrink..."

[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") >> $null

$server = New-Object Microsoft.SqlServer.Management.Smo.Server $contentdb.Server

$db = $server.Databases[$ContentDatabase]

Write-Host "Current size (Mb) : $($db.Size)"

Write-Host "Shrinking Step 1/2..."

$db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::NoTruncate)

$db.Refresh()

Write-Host "Shrinking Step 2/2..."

$db.Shrink(5,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)

Write-Host "New size (Mb) : $($db.Size)"

}

else

{

Write-Host "The timer job failed. Halting execution!"

break

}

You can find this script in my PowerShell repository on GitHub.