Debugging SQL Server Insert Hangs: Blocking, Transactions, and Batch Failures

If a batch-loading job repeatedly succeeds for the first files and then hangs or fails on the same later file, instrument the application and inspect SQL Server while the problem is happening. The goal is to identify whether execution stalls during the insert, commit, or another step—and then distinguish blocking, a bad row, transaction-log pressure, or resource exhaustion.

The examples below are diagnostic starting points, not proof of a root cause. Run them with appropriate database access, compare results with a known healthy batch, and avoid changing or killing sessions until you understand the transaction and its impact.

1. Identify the exact step that stalls

Log a batch identifier and a timestamp before and after the database operations. Log the number of rows and elapsed time, but avoid putting passwords, connection strings, or sensitive row contents in application logs.

print(f"Starting batch {batch_id}: {len(rows)} rows")
try:
    cursor.executemany(insert_query, rows)
    print(f"Insert finished for batch {batch_id}; committing")
    connection.commit()
    print(f"Batch {batch_id} committed")
except Exception:
    connection.rollback()
    logger.exception("Batch %s failed", batch_id)
    raise

Use your application's normal logger rather than printing raw database details in production. Exception logging should preserve the traceback, rollback should be attempted for a failed transaction, and the error should remain visible to the caller. If the driver or connection is left in an uncertain state after a failure, close it rather than silently reusing it.

• If the “insert finished” message never appears, investigate execution, locks, constraints, or database waits.
• If inserts finish but the commit message does not, investigate commit-time waits and transaction-log activity.
• If the exception fires, record the SQL error number and a safely redacted message, then inspect the failing input.

2. Inspect active sessions and blocking

When the job is stuck, check the relevant SQL Server instance promptly. sp_who2 can provide a quick overview of sessions and its BlkBy column, but a blocked session is only a starting point: trace the blocking session and understand what it is doing before taking action.

EXEC sp_who2;

For a more focused view, this query lists active requests that report a blocking session. It is read-only and shows request status, wait information, and the blocking session ID.

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

Dynamic management view visibility depends on the SQL Server version and granted permissions. If you cannot see other sessions, ask an authorized database administrator to run the check. A returned blocker does not automatically mean that the blocking session should be terminated.

3. Check for an open transaction

An earlier batch that did not commit or roll back can leave a transaction open and retain locks. DBCC OPENTRAN helps identify the oldest active transaction in the current database; it may not explain every blocked request, so correlate its output with the session and application logs.

DBCC OPENTRAN;

In application code, make transaction boundaries explicit. Commit only after the intended unit of work succeeds, roll back on failure, and close cursors and connections in a finally block or with the database driver's context-management pattern.

connection = pyodbc.connect(connection_string)
try:
    cursor = connection.cursor()
    try:
        cursor.executemany(insert_query, rows)
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    finally:
        cursor.close()
finally:
    connection.close()

Do not open a new connection for every file without closing it. Depending on the workload, reusing one connection or opening one per batch with reliable cleanup may be appropriate.

4. Check whether a particular row violates a constraint

If a batch fails quickly rather than truly hanging, the later file may contain a primary-key or unique-key duplicate, a foreign-key violation, or a value that exceeds a column's allowed size or type. After capturing the original error, reproduce the issue safely with a small, representative sample.

As a temporary diagnostic in a non-production environment, process a small sample row by row to identify which input triggers the error. This is slower than a batch insert and should not become the production ingestion strategy. Log a row identifier or redacted key—not sensitive payloads.

for row_number, row in enumerate(sample_rows, start=1):
    try:
        cursor.execute(insert_query, row)
    except Exception:
        logger.exception("Insert failed at sample row %s", row_number)
        raise

Do not skip failed rows and report the batch as successful unless partial ingestion is explicitly designed, recorded, and reconciled.

5. Check transaction-log space and wait types

A large transaction, log growth constraints, or slow log storage may delay writes. DBCC SQLPERF(LOGSPACE) reports transaction-log usage for databases on the instance; interpret the result alongside database configuration, growth settings, and available storage. A high percentage is a clue to investigate, not by itself a complete diagnosis.

DBCC SQLPERF(LOGSPACE);

When the request is actively waiting, its wait type can narrow the investigation. The examples below are clues rather than definitive diagnoses; use current wait data, the affected request, and the server's workload together.

Wait types can change quickly and may be cumulative or transient. Capture them while the symptom is present and include the session, query, and timing context.

6. Use error logs and tracing carefully

Review SQL Server error logs for relevant errors such as deadlocks, log-full conditions, or storage problems. Access to server logs and procedures such as xp_readerrorlog may require elevated permissions; ask a database administrator to retrieve the relevant time window rather than requesting broader access.

For deeper tracing, SQL Server Profiler or Extended Events can help show completed batches, errors, and deadlocks. Prefer a narrowly scoped Extended Events session where available, capture only what is needed, and keep an eye on event volume and sensitive data in captured statements.

7. A practical diagnostic sequence

1. Add timestamps around the batch insert and commit, and preserve the original error with its traceback.
2. When the job stalls, inspect sessions and the request's blocking session and wait type.
3. Check for an open transaction and review transaction cleanup in every batch path.
4. If the batch fails rather than waits, inspect constraint errors and isolate a redacted sample row in a safe environment.
5. Check log usage, server error logs, and focused event traces with a database administrator when needed.
6. Change one thing at a time and verify the batch with appropriate transaction, retry, and duplicate-handling behavior.

For example, if files containing 10,000 and 4,000 rows complete but a later 4,000-row file consistently fails, the repeated pattern narrows the search but does not establish a single cause. Check for a blocking transaction, an input-specific constraint violation, log pressure, or another active wait using evidence from the failing run.

Useful SQL Server system procedures

System procedures can help inspect objects and server activity. Some procedures in older reference lists are deprecated or have modern alternatives; verify availability and permissions for your SQL Server version before relying on them.

Older login and database-user procedures such as sp_addlogin, sp_droplogin, and sp_grantdbaccess are deprecated. Do not use them to change security as part of routine debugging; use current, reviewed login and user-management practices with least-privilege access.