Sunday, 19 April 2015

SharePoint Farm Freezing, Becomes Unusable Without An IISReset - A Lesson In Item-Level Permissions

Recently I was working with a client who's SharePoint 2010 environment had been poorly managed for years.  They had a single site collection on a single, large content database (650GB).  Not uncommon, however this environment also occasionally froze up, never loading any of the pages on any sites until an IISRESET was performed on the Web Front End (WFE).

It started off only happening once a month, then moved to once a week, a few times a week, to 5-10 times a day...

Begin Troubleshooting:

The issue was around too many lists, and items within those lists having their own permissions.

Each permission for an item counts as one row in the RoleAssignment table in the database.

apparently they say a healthy DB will only have 200,000 rows in that table.

Ours had 10,000,000 rows (You can check this by right-clicking the Database in SQL Management Studio and clicking Reports > Disk Usage by Top Tables.  Then look for the RoleAssignment Table).  and every time someone went to check permissions on a big list, bang.  it locks the table while it finds the data (which takes ages to traverse), meanwhile every other computer trying to do anything on the site dies while the table is locked.

The ONLY way to fix this is to remove any tables with HEAPS of item level permissions.

I got two handy scripts for this.  both below:


Script #1: a SQL script to find the lists that had the most item-level permissions

##SQL QUERY TO FIND INDIVIDUAL SECURITY SCOPES - PROVIDED BY MICROSOFT
select COUNT(ra.PrincipalId) as [Count],p.ScopeUrl from RoleAssignment ra with(nolock) 
join Perms p with(nolock) 
on p.SiteId = ra.SiteId and p.ScopeId = ra.ScopeId 
group by p.ScopeUrl 
order by p.ScopeUrl desc


Script #2: a SharePoint Powershell script that checks if a list has item-level permissions, then gives you the option to set every item in the list to inherit the Lists permissions:
  • Restore-Inheritance.ps1 "http://SPURL/SITE" "LIST NAME" true
############################################################################### 

##  ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ## 
############################################################################### 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) { 
    Add-PSSnapin "Microsoft.SharePoint.PowerShell" 
}


############################################################################### 
##  SET VARIABLES FROM ARGUMENTS ## 
############################################################################### 

$webUrl = $args[0]
$listName = $args[1]
$listInherits = $args[2]

# Varibale to hold document count
$count = 0

############################################################################### 
##  OPEN OBJECTS & RESTORE INHERITANCE ## 
###############################################################################

try {
# Open the web and list objects
$web = Get-SPWeb $webUrl
$list = $web.Lists[$listName]

# If the list should inherit, reset the role inheritance
if ($listInherits -eq $true) {
$list.ResetRoleInheritance()
Write-Host "Updated permissions on list." -foregroundcolor Green
}

# Get all items with unique permissions
$itemsWithUniquePermissions = $list.GetItemsWithUniquePermissions()
Write-Host $itemsWithUniquePermissions.Count "number of items with unique permissions found."

# Only update items if some exist
if ($itemsWithUniquePermissions.Count -gt 0) {
foreach ($itemInfo in $itemsWithUniquePermissions) {
$item = $list.GetItemById($itemInfo.Id)
    $item.ResetRoleInheritance()
$count++
}

# Display number of items updated
Write-Host "Updated permissions on $count items." -foregroundcolor Green
}
else {
Write-Host "No items with unique permissions exist, nothing to update."
}

# Dispose of web object
$web.Dispose()
}
catch [Exception] {
Write-Host "Exception encountered.  Please ensure all arguments are valid." -foregroundcolor Red
Write-Host $_.Exception.Message -foregroundcolor Red
}


REFERENCES & THANKS
https://gallery.technet.microsoft.com/office/PowerShell-to-Reset-Unique-d885a93f 

Thursday, 9 April 2015

Online Shopping is King

This is how bad it is to actually have to walk into shops and try and order something nowadays.

The bonnie lass & I recently scored a $100 Adairs* gift card and thought it was about time to go purchase something fancy with the "cash".  Normally we'd just look online, which we did, but we couldn't use the gift card online.  So we headed off to the Analog store.  After perusing the store for quite some time and being unimpressed with the range & the prices, we decided upon a quilt cover that we thought suited our needs.

After the store clerk tried calling multiple Adairs' stores in the region, it turned out they didn't have the quilt cover in our size (the ever illusive Queen size quilt).  No bother, we went back to the wall and chose another quilt cover that we were reasonably happy with.

Oh what's that you say?  You checked out the back, tried calling a few stores and you don't have a Queen in this print either? hmmm.  OK.  Well fuck it, we're here now, we've wasted enough time in this store, how about this one (choice number three).

"Oh yes, we have a Queen!" the store clerk declares, quite disbelieving of the fact herself.  "Would you like the matching pillow cases?" she asks next.  Oh dear, it turns out for some (villainous) reason this specific quilt does not come with pillow cases by default because the smug home designers out there thought it would be clever to allow people to mix and match, therefore allowing maximum creative design to enter the humble abode.

I'm not even sure where to begin with the logic here.  So I guess I'll just jot down some dot points as to why I think this is absurd.

  • Who wouldn't want matching pillow cases?
  • The quilt cover on it's own cost $129 (on special,  normally $170-something).
  • Who thinks they are clever enough to match a quilt better themselves by not choosing the same pattern?
Back to the story.  By this time we were exhausted trying to find something in their store we could buy, so we took the pillow cases when the store clerk asks "do you need chscshchschsch** pillows as well?".  No.

I noticed as she was walking to the counter with all the 3rd rate gear that the pillow cases were $58.95 each.  I knew the store had a special so made note to check the price as she scans them into the computer (let's not even dig into the fact that two pillow cases are the cost of a quilt cover).

Scan scan scan, total price $209.90.  We stare at each other, shrug at the hopelessness of it all and hand over gift card plus another $109.90 because it was time to get the hell out of there.  Now I can't belittle the clerk too much, she did recognise our hesitation and advised we could exchange if within 30 days.

Get home, put covers on, looks shit.  frustration came first, then sadness, then determination.

Packed all the gear back up, returned to the shop the next day.  asked to exchange, all good.  Found another quilt cover we decided would look better in the room (yeah we will never become interior decorators).  $109 WITH pillow cases.  ace.

still have $100.90 cents to spend.  sweet.  lets get those two cushions and that towel, that brings us to $209.95 cents.  perfect.

clerk 2 (who was probably frustrated that we returned something (how dare we)) then advises we still owe 0.05 cents.  when I heard that, what I really heard was "Please never return to our store again because it's just not worth your, or my, time".  Yes it's true we owed 5c, but is it really going to affect your bottom line to let us get away with it?

If there's any moral to this story it's that online shopping is way better than leaving the house.  Online stores should also start allowing customers to use their Gift Cards online.  Lastly, Never shop at Adairs.

-------------------

*Adairs is a homeware & manchester store for those not in the know.  Though hopefully not for much longer.

**The sound of her pronouncing the fancy french name for big pillows, also the sound of a tv tuned to no channel in particular, because I didn't care.