Skip to main content

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.

Speediful already retries automatically

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_replicate runs 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:

  1. Allow fewer concurrent writes. Lower Max Concurrent SQL Writes with configure_resources (for example to 1). It caps how many BULK INSERT statements 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.
  2. Use a smaller starting batch size. Lower the PK chunk size passed to SLAM_replicate / SLAM_query (the default is 100000; try 75000). 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.
  3. Retry the failed object on its own. Re-run SLAM_query (or SLAM_download_job with 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.
  4. Give SQL Server more memory headroom. Raise the instance's max server memory so 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.
note

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).

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.