Skip to main content

SQL Server Write Stalled (error 88321)

A write to SQL Server stopped making progress, so Speediful stopped the run.

Speediful writes retrieved data to SQL Server with BULK INSERT, and runs only a few of those statements at once (the Max Concurrent SQL Writes setting). When a query or SLAM_replicate has more data ready than that, the extra writes wait their turn. Waiting is normal: a replicate of many objects can queue writes for a long time, and Speediful lets them wait as long as writes keep finishing.

Speediful stops the run only when the writes themselves stop moving:

Message in the Results paneWhat happened
The write to <table> held a SQL Server write slot for 30 minutes without finishingOne BULK INSERT ran for 30 minutes without finishing. Speediful cancels it.
No write to SQL Server finished in 45 minutes while the write to <table> was waiting for its turnNo write finished anywhere in the run for 45 minutes.

Both messages end with the settings in effect, for example Settings in effect: MaxConcurrentSqlWrites = 3, MaxInFlightBytes = 2GB.

When this happens, Speediful:

  • cancels the stuck statement,
  • stops every write that was waiting,
  • starts no further objects (for SLAM_replicate), and
  • returns exit code 16, which the procedure raises as error 88321.

For SLAM_replicate, the summary in the Results pane lists the objects that completed before the stop, the object whose write stalled, and the objects that were stopped or never started.

Tables may be partly loaded. A table that was being written when the run stopped keeps the rows already committed. Re-run the procedure once the cause is fixed. With @exists_action = 'drop' or 'rename', the re-run replaces those tables.

What to do​

The usual cause is another session holding a lock on the destination table: an open transaction, a long report or an index rebuild. BULK INSERT waits for that lock with no time limit, so the write never finishes.

  1. While the run is still waiting, or when it happens again, look for blocking sessions:

    SELECT session_id, blocking_session_id, wait_type, wait_time, command
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0;

    A row whose command is BULK INSERT shows which session is blocking it in blocking_session_id.

  2. End or wait for the blocking session, or schedule the Speediful run when nothing else uses those tables.

  3. Re-run the procedure.

If nothing is blocking and SQL Server is overloaded instead (every write slow, memory or disk under pressure), lower Max Concurrent SQL Writes or Max In-Flight Bytes so fewer writes compete at once. See Resource Governance, and check the current values with SLAM_show_resources.

Speediful returns exit code 16, and the procedure raises error 88321. The table name, the time, and the settings in effect are in the Results pane.