SQL Server Out of Memory (errors 701 / 802 / 8645 / 8657)
SQL Server ran out of memory running BULK INSERT (error <number>).
Speediful writes retrieved data to SQL Server with BULK INSERT. SQL Server needs a
block of memory (a "memory grant") to run that statement, and when it cannot get one it
fails the statement, most commonly with error 701, "There is insufficient system
memory in resource pool 'default' to run this query," but also 802 (insufficient memory
in the buffer pool), 8645 (timeout waiting for a memory grant), or 8657 (could not get a
memory grant). Speediful treats all four the same way.
This is a SQL Server resource limit. It is not a problem with your data, the Salesforce object name, or the job ID. The same query or job succeeds once the server has room.
Before reporting this error, Speediful retries the failed write with a smaller batch size and resuming where it left off. These automatic retries never re-send or duplicate rows that already loaded. If the error message says it already retried (naming how many times, and how many rows loaded before it gave up), that means every automatic retry down to the smallest usable batch size still failed, and the steps below are what to try next.
It shows up most often on:
- a large or wide object (hundreds of thousands of rows, or many columns), and
- several queries or
SLAM_replicateruns at the same time, and/or - a SQL Server that is short on memory or is running on the same machine as Speediful, so the two compete for RAM.
What to do
Any one of these usually clears it; combine them if the object is very large:
- Allow fewer concurrent writes. Lower Max Concurrent SQL Writes with
configure_resources(for example to1). It caps how manyBULK INSERTstatements one run sends to SQL Server at the same time, so each has more memory available. Each run has its own limit, so also avoid running several large queries at the same time. See Resource Governance. - Use a smaller starting batch size. Lower the PK chunk size passed to
SLAM_replicate/SLAM_query(the default is100000; try75000). Smaller batches ask SQL Server for smaller memory grants, and give the automatic retry more room to halve before it hits the floor and gives up. - Retry the failed object on its own. Re-run
SLAM_query(orSLAM_download_jobwith the original job ID) for just the object that failed. The Bulk API job is still valid and fully re-downloadable. A re-run queries Salesforce again from scratch, so an append into the same destination table adds every row the query returns, including rows that already loaded. Narrow your query to the missing rows if you don't want duplicates. - Give SQL Server more memory headroom. Raise the instance's
max server memoryso it leaves enough for the operating system, or move Speediful off the SQL Server machine so they no longer share RAM. Ask your DBA before changing server-level settings.
Seeing error 9002 instead? That is a full transaction log, not a memory shortage. See SQL Server transaction log full for the fix, which is different (a log backup, not more memory or fewer concurrent writes).
Related exit code
When one of these errors reaches a stored procedure, Speediful returns exit code 14 and
the procedure raises an error whose message begins SQL Server ran out of memory running BULK INSERT (error <number>), naming whichever error actually occurred. Re-run with fewer
concurrent writes or a smaller batch size once the server has memory available.