Tags: Relational databasesScaleDesktopCommand line
The Service Requests list went past 5,000 items on a Thursday afternoon, and by Monday three things had stopped working. Instead of rows, the “Open by team” view showed a message saying the number of items in this list exceeds the list view threshold. The Power Automate flow that chases overdue requests failed on its Get items step, and the Excel workbook the service desk manager refreshes before the weekly review came back with an error where the numbers should be. Nothing had been changed over the weekend. The list had grown by a few dozen rows, and that was enough.
If that is your list, the threshold is the cause of all three, and it will not go away as the list grows. We build SPSync, which reads large lists through Microsoft Graph delta queries into a database, so part of this post describes how our reader behaves. The Microsoft limits come from Microsoft’s own documentation and are linked where they appear.
The attempted operation is prohibited because it exceeds the list view threshold
SharePoint words the refusal in two ways, depending on where you meet it. In a browser, Microsoft’s troubleshooting article for the error gives the message a list view shows:
The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.
From PnP PowerShell, CSOM or a REST call, the same refusal comes back as “The attempted operation is prohibited because it exceeds the list view threshold.” Microsoft Q&A threads quote it word for word, for example from a CSOM query on a large list and from a Get-PnPFolderItem call on a large library. Search for either wording and you arrive at the same limit.
The same troubleshooting article says SharePoint Online uses a feature called Large List Resource Throttling, with the list view threshold set at 5,000 items. In SharePoint Online that number is fixed. Your tenant admin cannot raise it for you, however much you ask.
The threshold applies to a single query, and the list itself can be far bigger. Microsoft’s SharePoint limits page puts the ceiling at 30 million items in one list. What fails is any one operation that would have to look at more than 5,000 rows to give an answer. A view that filters or sorts on a column with no index is the usual example, followed by an export that tries to pull everything at once.
That explains the Monday morning. “Open by team” filters on Team and Status, and neither column had an index, so SharePoint would have had to scan the whole list to find the open requests. It refused. The workbook and the flow were asking the list similar questions through different doors, and they got the same refusal.
Index and filter first, before you copy anything out
The first fix is an index, and for some lists it is the only fix you need. Index the column a view filters on, put that column first in the view’s filter, and the view comes back, because SharePoint can cut the set below 5,000 rows before it does anything else. For a service desk list, indexing Status and Team is a sensible first step. If people mostly look at their own open requests, it may hold for years.
Indexes run out, though. A list allows only a limited number of them (the list limits cheat sheet gives the figure and the Microsoft source), and the filter only helps when the indexed column by itself brings the result under the threshold. “All open requests” works until there are more than 5,000 open requests, and then it fails again. Sorting on an unindexed column, grouping, and a total across the whole list all hit the threshold, since each of them touches every row.
Some teams split the list by year or by region at this point. That brings the views back and breaks every report that needs the full history in one place, which is often why the list was kept as one list in the first place.
Why does Power Automate Get items fail past 5,000 items?
A flow meets the threshold in two ways. The SharePoint connector’s Get items action takes an OData filter query and an order by, and on a list past 5,000 items a filter or a sort on an unindexed column stops the action with a list view threshold error. Index the column the filter query uses and put that condition first, and the action usually runs again.
Volume is the second way. A flow that loops over every item has to turn pagination on, and Microsoft’s Power Automate limits page caps paginated items at 5,000 on the Low performance profile and 100,000 items on all the others. Past that, the same page tells you to split the work across several flow runs. A flow written against a few hundred requests two years ago was not built with either limit in mind. Our post on Power Automate Get items past 5,000, 100,000 and beyond covers which licences sit on the Low profile and how a flow that copies a list to SQL Server misses deleted items.
Power Automate is still the right tool for a lot of list work. A flow that reacts to a new request, sends a reminder or updates one item works the same on a list of fifty thousand as it did on a list of five hundred. The trouble starts when a flow is being used as a report or as an export, reading every item on a timer to answer a question about all of them. Our Power Automate comparison sets out which jobs belong in a flow and which belong in a copy.
How do you export a SharePoint list with over 5,000 items?
Export to Excel and Export to CSV both start from the view you have open, and they export the items and columns that view shows. When the view is blocked by the threshold, so is the export. The honest first answer is the same as for views: build a view that filters on an indexed column and stays under 5,000 items, export it, and repeat with the next slice, one per year or one per status, until you have the lot.
Slices are fine for a one-off. For an export someone repeats every week they turn into a chore, and the slices drift as items move from one status to another. A script that pages through the list by item id reads every item without a view, which is how most PnP PowerShell exports of a large list work, and our PnP PowerShell comparison covers what it takes to keep such a script going.
When the export exists to feed a spreadsheet or a report, the database copy described below replaces it. The table is the export, kept current between runs, and Excel connects to it directly. Our Excel reports use case shows that setup.
Power BI on a SharePoint list over 5,000 items
Power BI’s SharePoint Online list connector reads the list in pages, so a plain import of a list past 5,000 items is usually slow rather than refused. The failures come from elsewhere. Microsoft’s connector documentation says the 2.0 connector is held to 12 joins per query, so a default view with more lookup, person or workflow status columns than that fails the refresh. And a scheduled refresh that reads every item every time takes longer each month the list grows.
Start with the cheap fixes. Point the 2.0 connector at a default view that carries only the columns the report uses, which also keeps the lookups under the cap, and remove columns in Power Query before anything else runs. Our Power BI and SharePoint checklist lists the rest. When the report needs the whole list joined to other lists, a refresh against a database table is the lasting fix, and the Power BI on SharePoint lists use case and our post on fixing slow and failing Power BI refreshes walk through it.
How Graph delta query reads a large SharePoint list
Microsoft Graph has a
delta function for list items.
The first call carries no token. SharePoint starts enumerating the list and sends back a
page of items with an @odata.nextLink, and the caller keeps following those links until a
response carries an @odata.deltaLink instead. By then it has read every item. A later call
to that delta link returns only what was created, changed or deleted since.
None of this goes through a list view. Delta on list items does not accept a $filter
either, so there is no filtered query for the threshold to stop. SPSync asks for 200 items
a page and names every column it wants in the request. On the first sync of a list it
follows the next links to the end, writes every item to a table, and stores the delta link
in the destination database alongside the data.
Every run after that starts from the stored link. If forty requests changed since the last run, the reader fetches those forty and writes those forty, whatever the list’s total length. Once the first load is done, a list of fifty thousand items costs about as little to keep current as a list of fifty.
Delta has awkward corners of its own, such as tokens that expire with a 410 and deletes that some developers report never arrive. Our post on Graph list item delta: paging, tokens and missed deletes goes through each one, with the reconcile that checks the copy against the list.
Here is the setup for the service desk example, from the command line. The desktop app asks the same questions on screen.
spsync export-config --output servicedesk.yaml --pipeline-name servicedesk --prefix sr \ --destination-kind mssql --connection-string-env SERVICEDESK_DB_URL \ --dataset-name servicedesk --client-id $SPSYNC_CLIENT_ID \ --site-url https://contoso.sharepoint.com/sites/servicedesk \ --list "Service Requests" --list Teams --create-reporting-viewsspsync run servicedesk.yamlThe first run is the full load. After it, the servicedesk schema holds
sr_service_requests with one row per item, the Teams lookup as team_id and
team_value, and the shared sp_users table that Assigned To points at. Every row
carries sp_item_id, sp_modified and sp_deleted_at. An item deleted in SharePoint
keeps its row with sp_deleted_at set, so last month’s figures still add up. The view
that failed on Monday becomes a query:
SELECT t.title AS team, COUNT(*) AS open_requestsFROM servicedesk.sr_service_requests AS rJOIN servicedesk.sr_teams AS t ON t.sp_item_id = r.team_idWHERE r.status <> 'Closed' AND r.sp_deleted_at IS NULLGROUP BY t.titleORDER BY open_requests DESC;SQL Server and Postgres have no list view threshold. Add an index on status if the query
runs often, and sort on whatever column you like.
The 12-lookup limit on a wide list
The second SharePoint limit a large list runs into concerns width. Microsoft documents a list view lookup threshold of 12 joins per query, where each lookup, person or workflow status column counts as one join (software boundaries and limits). A service desk list often has a lot of these: requester, assignee, team, site, category, asset, an escalation contact. Past 12 lookup columns in one query, SharePoint refuses it.
A Graph read is subject to the same cap, which is why SPSync’s reader never asks for more
than 12 lookup columns in one request. For a wider list it splits the column selection
into batches, reads each batch in turn and merges the results by item id before anything
is written. That batching has been tested against our Graph simulator only, and not on a
live list with more than 12 lookup columns. It asks for a single person column by its id alone, which keeps that column
from adding a join, and then resolves the person through the site’s user list into the
shared sp_users table. In the database, each lookup is a pair of columns you join on,
and a report can join as many of them as it needs. The
lookup column threshold post covers what
counts towards the 12 and the error each tool shows past it.
SharePoint throttling during a full load
The first read of a large list is the moment SPSync asks the most of SharePoint, so it is also where throttling matters. SharePoint meters each app per tenant in resource units. In Microsoft’s throttling guidance, a delta request with a token costs 1 resource unit and a multi-item read costs 2 resource units, so a page of the first load costs twice what a later delta round does.
The rules SPSync follows are written down in our architecture decision on throttling. When
SharePoint answers 429 or 503 with a Retry-After header, the client waits exactly that
long before it tries again. It meters the resource units each response reports, per tenant
per rolling minute and per day, and slows down at 80 percent of the published budget. All
the lists in one run share one budget per tenant, with four requests in flight at a time by
default, and two rounds never run against the same list at once.
The trade-off is time. A first load paced to leave room for everyone else in the tenant takes longer than a script that sends as fast as it can, right up until that script gets a 429 and starts competing with the people using SharePoint. If you would rather run the first load at a quiet hour, start the command-line tool from your own scheduler. Our post on Graph throttling and AI pipelines works through the resource-unit arithmetic in more detail.
How large a list has SPSync been tested on?
On a live SharePoint Online tenant, the largest run so far read a 10,000-item Service Requests list, past the threshold twice over, along with three smaller lists, into SQL Server. That run read 10,303 items, made 115 Graph calls and took 1m 52s from start to finish with the desktop app on 18 September 2026. The figures and how they were measured are on the SharePoint to SQL Server page.
Against a synthetic Graph, the same reader has done a full load of a 1,000,000-item list and then a delta round of 10,000 changes on it. The slowest recorded full load took 112 s and the delta round 0.6 s. Those numbers come from a scripted fixture with no tenant network in the way, so they show the reader stays correct at that size. They are not a promise of how long your tenant will take, which depends on its throttling budget and the network between you and Microsoft.
Reconcile, weekly by default, as a QA check
Delta keeps the table current between runs. A reconcile answers a different question,
which is whether the table and the list match right now. It reads the id and version of
every item in the list (one paged listing per list) and compares them with the table.
Items that no longer exist in SharePoint get sp_deleted_at set, and items whose version
differs are read again.
The desktop app and SPSync in your Azure run it on their own, weekly by default, and the interval is configurable. From the command line you can run one whenever you want:
spsync reconcile servicedesk.yamlThere are two good moments to run one by hand. The first is before you hand the table to the people who own the list, as evidence that what they see in a report matches what they see in SharePoint. The second is after an outage, on your side or Microsoft’s, when your database server was down for a day or Microsoft 365 was unavailable and you want the copy back in line without waiting for the schedule.
When an index is enough
Not every list that crosses 5,000 items needs a database behind it. If one or two views broke and they filter on a column that cuts the result well below the threshold, index that column and carry on. If the list is mostly read one item at a time from a form or a flow, it will keep working long after it passes fifty thousand.
A copy pays for itself when the questions are about the whole list: counts by team, a backlog trend over a year, a Power BI report, a join to another list. Those are the questions the threshold blocks however you index. The 5,000-item threshold use case walks through choosing the lists and the destination. SQL Server and Postgres are the usual choices for a service desk, and SPSync writes to many other destinations too, one replication each, so the same list can feed a warehouse as well. When your reporting already lives somewhere we do not write to, say so and we will look at it.