Solved

Sharepoint 2010 5000 list threshold issue

Posted on 2016-10-23
18
44 Views
Last Modified: 2016-11-04
I have a user who has created a view to display all documents checked out to him across all folders. Apparently this report used to work correctly but recently he has been receiving the message that he's exceeded the 5,000 item list threshold. I spoke with him about it and was assured that at most he would have 500 or so documents checked out.

This morning I made (temporary) changes to the site to allow the view to work. Changing to 20,000 still gave the error, changing to 200,000 allowed the view to finish.

But there is only 473 items in the view?

No idea why this would be happening. Clearly I can't leave the value at 200,000 but he needs this view to work.

Is this a bug in Sharepoint or is there some other reason the view is normally failing?
0
Comment
Question by:tonydav67
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41856939
Here are a few points;

Check the total number of items. That will help determine where you stand.

You mention that he created a view, and in the third paragraph you mention another view that only shows 473 items. These may be separate view, the one the user created produced results that were over the threshold, and the other view that may be filtered differently, therefore fall under the threshold.

Double check deeply the view and you may find the answer. Also, keep in mind that view are security trimmed so that may change the number of items shown.

Good luck...
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41857607
There's only one view which is the one he created. My process was:

Log with his credentials
Change to his filtered view
 - receive error message (> 5,000 list items)
In another session change list view limit to 200,000
In the original session change to that view, works with 473 items

The 473 matches what he thought he would have had.

If I run it on the admin account it works fine with the original limit (I only have 2 checked out items)

There are a lot of files. Not sure how many (or how to work it out) but when I run an "all files" view with a 20,000 limit it fails. Not sure how this impacts on the original query?
0
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41857622
When you go to the "All Site Contents" page you will see the number of items in a list or library. Determine the number of items and set the list threshold accordingly.

The relationship between setting the list view limit to 2000,000 and 473 items display seems to be an anomaly. To check cleanly you may want to create a clean, new view. With no filters. I have learned that users have a magical way to do things that can throw off IT people like us.

Good luck...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41859325
Any luck?
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41859455
The "projects" folder (which I've set this view up on) has 54300 items.

Created a new view and still received the error message when applying the "checked out to me" view.

Changed the limit to 55000 and it works.

But the result returned is only 480 items.

I don't really follow - I had understood the list limit to relate to the results returned, not the total items in the database. Am I wrong on this?
0
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41859462
No, you are not wrong. The list view threshold is just that, the number of items that can be viewed in a view. The total number of items in the database does not anything to do with the list view threshold.

What results do you have when using a standard filter such as date, user, some other no system filter. "Checked out to me" filter may be adding some unexpected criteria to the mix.

Hope that helps...
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41859487
I created another view that filtered "modified by" is equal to "[Me]". Same issue. This returned 2509 for that user.
0
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41859503
This is hard to troubleshoot without being there. Just systematically test until the results are what you expect. I know that is not a good answer, but that is all that can be done right now. I could throw out things to test, but I am sure you know what to test in your situation better than I do.

Good luck...
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41859514
Thanks. Not really sure what to do now. Might need to log an incident with Microsoft.
0
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41859517
Just to make sure, you are adjusting the list threshold in Central Administration? It is a web app scoped setting.
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41859522
Yes that's correct.
0
 
LVL 2

Author Comment

by:tonydav67
ID: 41859530
I have a feeling I'm not interpreting this correctly. From the help information:

List View Threshold
Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked.

The word "process" rather than "return" tends to indicate to me that this is why the query isn't working.
0
 
LVL 18

Assisted Solution

by:Walter Curtis
Walter Curtis earned 500 total points
ID: 41859539
0
 
LVL 2

Accepted Solution

by:
tonydav67 earned 0 total points
ID: 41859548
I think I've fixed the issue.

I investigated the index and it  looks like there was some sort of weird compound index for this field. Deleted and setup a new simple index for the "checked out" field. Now works with a 5000 limit.

The other "modified by" test still fails so if he wants this report I'll most likely need to create an index.
0
 
LVL 18

Assisted Solution

by:Walter Curtis
Walter Curtis earned 500 total points
ID: 41859560
Great work!!!

just an an FYI- I have had list thresholds as high as 65k before with no problems. But of course that all depends on overall farm performance and what else is going on.

Have a good one.....
1
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41868310
Please do not abandon this question.
0
 
LVL 2

Author Closing Comment

by:tonydav67
ID: 41873675
I found the answer via Microsoft Technet forums.
0
 
LVL 18

Expert Comment

by:Walter Curtis
ID: 41874033
Thanks
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This collection of functions covers all the normal rounding methods of just about any numeric value.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question