Solved

Sharepoint 2010 5000 list threshold issue

Posted on 2016-10-23
18
43 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 17

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 17

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 17

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 17

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 17

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 17

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 17

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 17

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 17

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 17

Expert Comment

by:Walter Curtis
ID: 41874033
Thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA in SharePoint 3 56
SharePoint 2010 Foundation Gatherer 10 82
SP2010 Parent Child Relationship 3 48
SharePoint 2013 with K2 5 29
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

756 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