SyteLine freezing for everyone? Find the SQL head blocker, and stop deadlock error 1205

Blocking vs deadlocks

Step 1: Find the head blocker

This query lists every session that's waiting on another, plus the sessions they wait on. It's standard SQL Server, nothing SyteLine-specific:

SELECT  r.session_id,
        r.blocking_session_id,
        r.status,
        r.wait_type,
        r.wait_time / 1000          AS wait_seconds,
        s.login_name,
        s.host_name,
        s.program_name,
        t.text                      AS running_sql
FROM    sys.dm_exec_requests  AS r
JOIN    sys.dm_exec_sessions  AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0
   OR   r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
ORDER BY r.blocking_session_id, r.wait_time DESC;

The head blocker is a session that appears as someone's blocking_session_id but isn't blocked itself. Note: a head blocker with no active request (an open transaction that's sitting idle) won't show in sys.dm_exec_requests at all. If the chain points at a session you can't see here, look it up in sys.dm_exec_sessions and check its open_transaction_count.

Step 2: Confirm what it is before doing anything

Never kill a session just because it's at the top of the chain. Find out what it's running:

SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.login_time,
        s.open_transaction_count,
        ib.event_info AS last_statement
FROM    sys.dm_exec_sessions AS s
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE   s.session_id = 123;   -- the head blocker

(sys.dm_exec_input_buffer needs SQL Server 2014 SP2 or later. On older versions use DBCC INPUTBUFFER(123).)

In SyteLine the usual head blockers are:

• A background task with a much wider range than intended: a posting run, packing slips, a mass update. The host is usually the TaskMan or utility server. Check its parameters in Active Background Tasks. See Why a SyteLine task runs for days.
• A heavy report or query run against the live database, often from a reporting tool or Excel, holding locks while it reads.
• An integration that opened a transaction and never committed, often after an error in the middle of a batch.
• A user session in a transaction that's waiting on something, which is rare but possible with customized forms.

Step 3: Wait, fix or kill

• If it's a legitimate long job that's nearly done, letting it finish is often the least disruptive choice.
• If it's a wrong-range task or a runaway query, have the system administrator run KILL <spid>, then watch the rollback with KILL <spid> WITH STATUSONLY. A rollback can take as long as the work it undoes, and everyone stays blocked until it finishes, so don't restart services in the middle of it.
• If it's an integration, stop the integration first so it doesn't immediately reopen the same transaction.

After any kill, check what the session had already processed before re-running it, so nothing is posted or printed twice.

Deadlock error 1205

The full message reads: "Transaction (Process ID n) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Nothing is stuck. SQL Server already cancelled one side. Often the user simply retries and it works. If it keeps happening, find the pair that collides:

-- recent deadlock graphs from the built-in system_health session
SELECT  xed.value('@timestamp', 'datetime2') AS deadlock_time,
        xed.query('.')                       AS deadlock_graph
FROM (
    SELECT CAST(target_data AS xml) AS target_data
    FROM   sys.dm_xe_session_targets AS st
    JOIN   sys.dm_xe_sessions        AS s ON s.address = st.event_session_address
    WHERE  s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS d
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed)
ORDER BY deadlock_time DESC;

Open the graph and look at the two processes. In SyteLine the usual pairs are two utilities scheduled at the same time over the same tables, an integration updating orders while users edit them, or a custom procedure that updates tables in a different order from the standard code. The fixes are usually scheduling (run them apart), batching (smaller transactions), or changing the custom code's update order, not database settings.

Stop it coming back

• Alert on blocking. A SQL Server Agent job that checks for any session blocked longer than a few minutes turns a frozen morning into an email. The full script is in Get an email when SyteLine is blocked.
• Watch the queue daily. Anything in Active Background Tasks that has been RUNNING far longer than usual is the most common head blocker.
• Keep reports off the live database where you can. Point reporting tools at a reporting copy or read-only views. See Connecting Power BI and Excel to SyteLine safely.
• Spread heavy utilities apart in the schedule, especially at month-end.
• Keep transactions short in custom code and integrations. Commit in batches, and always commit or roll back on error.

FAQ

Why is SyteLine frozen for all users?

Usually one SQL Server session is holding locks the others need. Find the head blocker with the blocking query above and check what it's running.

What is deadlock error 1205 in SyteLine?

Two processes were each waiting on the other, so SQL Server cancelled one of them. Retrying usually works. If it repeats, find the colliding pair in the deadlock graph.

Is it safe to kill a blocking SyteLine session?

Only after confirming exactly what it is. Killing it rolls back its uncommitted work, and the rollback can take a while.

How do I stop SyteLine blocking from happening again?

Alert on long blocking, check long-running background tasks daily, keep reports off the live database, and spread heavy utilities apart.