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 pane | What happened |
|---|---|
The write to <table> held a SQL Server write slot for 30 minutes without finishing | One 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 turn | No 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.
-
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, commandFROM sys.dm_exec_requestsWHERE blocking_session_id <> 0;A row whose
commandisBULK INSERTshows which session is blocking it inblocking_session_id. -
End or wait for the blocking session, or schedule the Speediful run when nothing else uses those tables.
-
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.
Related exit code
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.