Tags: Relational databasesAutomationDesktopCommand line
The flow worked for a year. It ran every night, read the Requests list with Get items and wrote each item into a SQL Server table that a Power BI report reads. Then someone noticed the table held exactly 100 rows, or that it had stopped growing at 5,000, or that a request deleted in March was still on the dashboard in October. The searches that follow are some version of “power automate get items limit”, and the answers are spread across three Microsoft pages and a lot of forum threads.
There are three different caps behind those symptoms, and each belongs to something different. The first is a default on the action and the second is SharePoint’s list view threshold. The third is set by the licence of whoever owns the flow. This post takes them in the order a growing list meets them, then covers the job a sync flow does worst, which is noticing deletes.
Why does Power Automate Get items only return 100 items?
Microsoft’s two pages on the action disagree about the default. The guidance on Get items and Get files says that with only the site address and list name filled in, the action returns 100 items from the list. The SharePoint connector reference describes the Top Count parameter as defaulting to all items. A flow that stops at exactly 100 matches the guidance page, and the fix is the same whichever page describes your tenant today: turn pagination on and set a threshold, or set Top Count. A flow built against a test list of forty items never shows the problem, and the first sign is a table that matches SharePoint for a while and then falls behind.
There are two ways past it. The Top Count field under advanced parameters sets how many items you want, and the same guidance says you can raise it as far as the list view threshold of 5,000. The second is pagination, which is the one to use on any list that might grow.
Turning pagination on
Open the Get items action, go to Settings, switch Pagination on and type a number in Threshold. The action then keeps following SharePoint’s next-page links until it has that many items or runs out. Set the threshold above the size you expect the list to reach in the next year or two. A threshold of 5,000 on a list of 6,000 items gives you the first 5,000 and no error, which is the worst kind of failure for a sync, because the run history shows green.
Pagination also changes what a filter query does. The same Learn page lists a known limitation: on a list with more than 5,000 items, a Get items call with a filter query can return nothing when none of the matches are in the first 5,000 items, and the fix Microsoft gives is to turn pagination on.
Why does Get items stop at 5,000 items?
Two different limits share the number. The first is SharePoint’s list view threshold, configured at 5,000 items by default in SharePoint Online. A Get items call whose filter query or order by uses a column without an index has to scan the whole list, and on a list past 5,000 items SharePoint refuses the query with a threshold error. Index the column and put that condition first in the filter. Our post on what breaks past 5,000 items quotes the error text and the index fixes in full, and the 5,000-item limit use case covers the same threshold for views, exports and reports.
The second is the pagination cap, and that one surprises people who already turned pagination on and set a threshold of 50,000. Microsoft’s Power Automate limits page caps paginated items at 5,000 on the Low performance profile and 100,000 on all the others. A flow on Low gets 5,000 items however high the threshold is set.
Your licence sets the cap, and so does the flow’s owner
The same limits page has a table of which plans sit in which profile. Low includes the Microsoft 365 plans, Power Apps Plan 1 and per app plans, Power Automate Plan 1 and every licence trial. Medium includes Power Automate Premium, the plan previously called per user. So a flow built by someone using only the Power Automate that comes with Microsoft 365 is on Low, with the 5,000-item cap.
The profile follows the flow’s owner. Learn says that if the original owner leaves the organisation, the flow reverts to the Low performance profile. A flow that read 40,000 items a night for its creator can start returning 5,000 the week after they leave, with nothing in the flow changed. If a sync flow matters, check who owns it and what that person is licensed for. Move it to an owner whose licence will outlast any one employee, or assign it a Process licence, which Learn puts on the High profile.
The daily request budget
Each profile also carries a budget of Power Platform requests per 24 hours: 10,000 requests on Low and 200,000 requests on Medium. Learn is specific that retries and requests from pagination count as action runs, and so does every action inside a loop. A flow that pages through 20,000 items and then runs a condition and an upsert for each one spends tens of thousands of actions on one night’s copy, before any retries.
What happens past 100,000 items?
The cap does not move above 100,000 on any profile. Microsoft’s answer on the limits page is to trigger multiple flow runs over your data, and the Apply to each loop has the same 5,000 and 100,000 ceilings, so the work has to be split before the loop as well as before the read.
The usual pattern is a parent flow that works out the slices and a child flow that
handles one each. Slicing by ID range is common, with a filter such as
ID ge 1 and ID le 90000 for the first child run and the next range for the second.
Test the filter on your own list before relying on it, and if you slice on any other
column, index it first, or the slice itself meets the 5,000-item threshold. Keep each
slice comfortably under 100,000 so growth between runs does not push one over.
The SharePoint side has its own limit too. The SharePoint connector reference throttles each connection at 600 API calls every 60 seconds. Paged reads stay well inside that, but a flow that also calls Get item or Update item once per row reaches it on a large list, and the run slows to wait.
Copying a SharePoint list to SQL Server with a flow
The flow behind most “power automate sharepoint list to sql server” searches has the same shape. A recurrence trigger, Get items with pagination on, an Apply to each over the results, and inside the loop a Get row on the SQL Server connector followed by Insert row or Update row depending on whether the item already exists. It works, and on a list of a few thousand items it is a reasonable thing to build.
Three costs grow with the list. The SQL Server connector is Premium in Power Automate, so the flow’s owner needs a premium plan, which also puts the flow on the Medium profile. The connector’s own throttling in the shared environment allows 100 create, read, update or delete calls per connection every 10 seconds. One write per item on a 100,000-item list is 10,000 seconds of SQL calls at that rate, close to three hours, before the Get row lookups that decide between insert and update. Teams that hit this move the writes into a stored procedure that takes a batch of rows as JSON, which cuts the calls and adds a piece of T-SQL someone has to maintain.
The third cost is the schema. Each Insert row and Update row maps list columns to table columns by hand. A column added to the list does not reach the table until someone edits the flow, and lookup and person columns arrive as nested objects the flow has to pull an ID or a display name out of. Our comparison with Power Automate sets out which flows are worth keeping and which ones are doing a sync job. For the wider choice, SharePoint to SQL Server tools compared puts the flow next to the other tools that copy a list into SQL Server.
Why does the flow not delete rows in SQL?
Because nothing tells it an item has gone. Get items returns the items that exist today, so a deleted item is an absence, and a flow that only inserts and updates leaves its row in the table for good. Reports built on that table go on counting closed and cancelled requests that SharePoint no longer has.
The obvious fix is a second flow on the When an item is deleted trigger. The SharePoint connector reference says that trigger can only be used by site collection admins of the site where the list is located, which rules it out for many of the people who build these flows. It also fires once per delete, so a run that fails or is switched off for a day loses those deletes with no way to replay them.
The scheduled alternative is a full comparison. Read every ID from SharePoint, read every ID from the SQL table, and delete or flag the rows whose ID is missing from the list. That doubles the read on every run, it is subject to the same 100,000-item pagination cap on the SharePoint side, and it is easy to get wrong in the direction that matters most. A Get items call that stops early, because of a threshold or a throttled page, makes every item after that point look deleted. Any comparison flow needs a guard that refuses to delete when the count it read is lower than the count it expected.
When a flow is the right tool
A lot of list work belongs in Power Automate and should stay there. Flows such as an approval when an item is created, or a Teams message when a status changes, react to a single event, and their cost does not grow with the size of the list. A nightly copy of a list that will stay under a few thousand items, where nobody deletes items and the columns rarely change, is fine as a flow too.
The signs that a flow has become a sync job are the ones above. It reads every item on a timer, its threshold has been raised more than once, someone has asked why deleted items are still in a report, or a column added in SharePoint has been missing from the table for weeks. The limits cheat sheet lists the SharePoint limits behind each of those, with a Microsoft source for every one.
Reading the list with delta into SQL Server
SPSync replaces the copy, and only the copy. It is one way: SharePoint stays where people edit, and the database is a read replica of the lists you pick. The first run reads the whole list through Microsoft Graph, paging past the view threshold without needing an index. On our dev tenant, a full load of four lists into SQL Server, one of them past the threshold, read 10,303 items with 115 Graph calls in 1m 52s. After that, each run asks Graph for the changes since the last one, every five minutes by default.
Deletes come through the same delta read, and the row stays in the table with its
sp_deleted_at timestamp set, so a report can filter deleted items out or count them.
A reconcile, weekly by default and configurable, compares every item ID in the list with
the table. It works as a QA check you can run at any time, and as the way a replica
catches up after an outage on your server or in Microsoft 365. Hard deletes are available as a
per-replication setting in the YAML config the command-line tool runs. A new column in
the list appears in the table on the next run, lookups arrive as ID and value columns you
can join on, and multi-choice columns become their own child table.
It also takes the Premium connector out of this job. SPSync is licensed per machine and per destination family, with no per-flow or per-run charge; the licensing page explains how that works for unattended runs. Your approval and notification flows keep running as they do today.
Setting it up
The SharePoint to SQL Server page shows the tables SPSync writes and the columns in them, and the Azure SQL page covers the same for Azure SQL Database. The replacing Data Factory and flows use case walks through switching a report from the flow’s table to the replica, and Seven steps from a SharePoint list to a SQL Server table covers the setup in the desktop app and on the command line.
Besides SQL Server and Azure SQL, SPSync writes to Postgres, warehouses, lakes and search indexes, among others, and the same lists can go to several of them, one replication each. If the destination you need is missing, tell us.