Solved

Sharepoint 2010 5000 list threshold issue

Posted on 2016-10-23
18
40 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
  • 10
  • 8
18 Comments
 
LVL 15

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 15

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
 
LVL 15

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 15

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 15

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

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 15

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 15

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 15

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 15

Expert Comment

by:Walter Curtis
ID: 41874033
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now