At the moment I’m sitting in the Gman garage in Antwerpen waiting on my car to be fixed. So excellent time to write some blog posts that I’ve been putting off for too long now.

Some time ago there was a problem at the client with a list of 10-20k items big. The discussion on how large a list is and should it be put in SP I’ll leave for another time, so the list was there and we had to solve it.

A simple query in an xsltlistviewwebpart would be the beginning of a big issue.

I’ve added some very interesting reads at the bottom of this article but all didn’t solve my issue.

Below was one of the first issues that we had.

Error_ItemLevelPermission

If you Google this one, a lot will tell, just set the threshold of the web application higher.

well, first ask yourself, why is this limit 5000 items?

To minimize database contention, SQL Server uses row-level locking as a strategy to ensure accurate updates without adversely affecting users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at the same time, then it is more efficient for SQL Server to escalate the lock to the entire table until the database operation is completed. When this lock escalation occurs, it prevents other users from accessing the table

This small part in a very large blog article on Technet explains why.

Meanwhile the threshold was already set at 55000 items on production = NOT GOOD

Sure it solved the issue for a while but it created others elsewhere.

So back to the drawing board, why do we have this issue. Why is a query generating this particular error.

I will spare you the details on looking into this issue and skip to the solution.

It was because not only these 10k-20k large list was big but also all the items in it had broken security. So we had item level security in place. This is why we had this issue.

A little re engineering later (using folders with broken security and putting the items in those folders solved it) and all was good to go. From 20k items we went to 35 folders with broken security and the query would run nicely now.

A fun fact: working with large lists it’s often suggested to use the content iterator. Well the content iterator in the back-end applies a row limit, recursive node and listitemcollectionposition. In doing so it queries in batches but uses SPQuery just as well.

So CAML isn’t dead yet hehehe

 

Sneak preview: Did you know that our Caml Designer 2013 now supports SP 2010 as well, so you can work with the newly (and cool) designed CamlDesigner 2013 on both SharePoint versions. (Release is planned for 27 April).

 

Some good reading:

I’ve divided this into a table because of the many SharePoint versions:

Title (link to articles)

SharePoint Version

Comments

Software boundaries for large lists in SharePoint
2013

2013

 

Manage lists and libraries with many items

2013 & Office 365

 

Must do course

2013

 

Technet article

2010

 

Blog article from Steve Peschka

2010

 

Blog article from joel oleson

2010

 

Blog article from the SharePointBaker
(a buddy of mine 😉 )

2010

 

Working with large lists

WSS 3.0

It says WSS 3.0 but it still applies for the other
versions

Official read

2007

 

Wilke (a collegue of mine) has developed a very interesting tool to learn about the SPWebConfigModification  API (link).

This tool  gives an example of web config modifications without actually applying them to the web.config

Very well written article so don’t wait any longer 🙂

hey everyone

I’m now working at Ordina Belgium (second day on the job) so it could be that my blogging is being put on hold for the time being. But when I find something worth blogging about, be sure you’ll be the first to know 😉

kind regards

andy

hey everyone

A few days ago I had a stupid problem. I’ve installed updates on the server but I needed to restart it.

Ok restart via de update button , losing rdp connecton ok that’s normal, almost waiting an half hour to come back online (not normal). After half an hour the server still wasn’t online. So went to the host screen of the (luckely) virtual machine. It hanged in shut down mode.

When you click restart, one of the first services that’s being shutdown is RDP host service.

But if your server hangs after that , there is no way to have a remote solution to get back on the server (not without third party apps and stuff).

So always restart your server via de command prompt :

  • to restart : shutdown /r /f  (/r = restart,  /f = force)
  • for shutdown: shutdown /s /f  (/s = shutdown)

hope it helps someone

kind regards

andy

This is a short blog for the people who are looking for IISAPP (in the command prompt gave the W3Wp process id’s and their link to the IIS application pool) in windows 2008.

Well in short it’s gone, but no worries “%windir%system32inetsrvappcmd.exe list wp”  is the new command.

Why would you use it? if you must debug and wspbuilder isn’t working correctly (normally a wrong DDL) and you need to attach it manually. If you have 10 W3WP”s services, you can ofcourse connect to all of them, but if you are with 2 developers on 1 dev machine, this can be quit annoying.

So main rule, only connect to your W3Wp and not all 10 🙂

Hope it helps someone

Hey everyone

Today I was testing a migration from our clientportal from MOSS 2007 to SharePoint 2010.

Well after the stsadm preupgradecheck and a few error the rest went smooth.

Once migrated I noticed that the look & feel still 2007 was, this was ofcourse of the visual upgrade setting.

Well you can visual upgrade via 2 ways.

(top level site) Site Actions -> Site Settings -> and in site collection administration Visual Upgrade

or the way I used is via PowerShell (let’s admit it , we’ve all been waiting to use this wonderful and easy language)

well here’s the powershell script (and ofcourse you can use some in C# code.

$site = Get-SPSite(” sitecollectionurl “)

foreach ($web in $site.AllWebs) { $web.UIVersion = 4; $web.Update() }

The thing is UIVersion = 4 sets the view to SharePoint 2010

UIVersion = 3 sets the view to SharePoint 2007.

sources:

www.sharepointdevwiki.com

http://mysharepointwork.blogspot.com/

hey everyone

Some time ago I had to do a restore of a SharePoint intranet. Nothing special here, using the stsadm commands backup and restore, so far so good.
But the problem came during the restore process, the server took so much memory that it almost flatlined. Even opening a window took ages.

Now what was the problem?
Out of the box SharePoint installs the embedded SQL server.
But if you are not a DBAdmin like me (I know SharePointers need to know Windows Server to the bone (IIS, AD, DNS, …) you would probebly not know this.
The embedded SQL server has a memory issue and that is, by default it requires all the memory and this is where the problem was.
You’ll need to go via a SQL Management studio express program to your (.pipeMSSQL$MICROSOFT##SSEEsqlquery) embedded sql server.
Than rightclick your sql server and choose properties -> memory options and here change the default maximum server memory to whatever is right for the server.

Some pointers, look at the purpose of the sql server, if you need to index 200.000files don’t give it 512MB. Also look at the memory of the server it self, server has 4GB memory, don’t give 6GB 🙂

Hope this helps someone