?
Solved

Sharepoint 2010 5000 list threshold issue

Posted on 2016-10-23
18
Medium Priority
?
53 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 19

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 19

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 19

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 19

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 19

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 19

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 19

Assisted Solution

by:Walter Curtis
Walter Curtis earned 2000 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 19

Assisted Solution

by:Walter Curtis
Walter Curtis earned 2000 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 19

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 19

Expert Comment

by:Walter Curtis
ID: 41874033
Thanks
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

770 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