Why a SyteLine task runs for days: one wrong range, and invoicing stops

Free script: The blocking check from this guide is ready to run, with the KILL step commented out, in my free SyteLine SQL diagnostic scripts.

What happened

This was on SyteLine 9, at a manufacturer I worked for.

• A user ran packing slips for a range of customer orders. The end of the range had one digit too many: x1004 to x10005 instead of x1004 to x1005.
• SyteLine accepted the range and started the task. Nothing on screen looked wrong.
• The task kept running for days.
• Small reports carried on running normally. The background queue wasn't jammed, so there was no obvious alarm.
• Invoicing stopped. That's when it was noticed, days later, by which point shipments were waiting to be invoiced.

The pattern is the useful part. It's not "the system is down." It's one process that depends on the same orders and shipments as the stuck task, while everything else carries on.

Why invoicing was the one that stopped

Packing slips and invoicing work on the same customer orders and shipments. A task that's still working through a huge order range can hold locks on those records. When invoicing needs the same records, SQL Server makes it wait until the first task lets go. A small report that reads other data never needs those locks, so it runs fine.

That's why the symptom is so selective, and so easy to blame on the wrong thing.

How to spot it

1. Look for long runners in the queue

Open Active Background Tasks and look for anything with a status of RUNNING whose start time is hours or days ago. Packing slips for a normal range take minutes, so a packing slip task that started yesterday is a red flag.

2. Read its parameters

Open the task and look at the ranges. Typical slips:

• an extra digit at the end of a range (x1005 typed as x10005)
• start and end the wrong way round
• a blank "to" value, meaning "everything from here on"
• the wrong prefix or site

3. Confirm the blocking in SQL Server

Ask the DBA, or anyone with read access to SQL Server's management views, to run a query like this. It's a standard SQL Server query, nothing SyteLine-specific:

SELECT  r.session_id,
        r.blocking_session_id,
        r.status,
        r.wait_type,
        r.wait_time / 1000      AS wait_seconds,
        DB_NAME(r.database_id)  AS database_name,
        t.text                  AS running_sql
FROM    sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE   r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

If the invoicing session shows a blocking_session_id, that number is the SPID (session ID) of the session holding it up. That's exactly what we found: invoicing was blocked by the packing slip generator's session.

How we fixed it

This is the procedure we used, in order. Step 2 is the one that matters most.

1. Find the blocking SPID

Run the blocking query above. Note the blocking_session_id on the invoicing row. That's the session to investigate.

2. Confirm it's the wrong-range task before touching it

Never kill a session just because it's blocking something. Confirm what it is first:

-- what the blocking session is running, and with which parameters
SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.login_time,
        r.start_time,
        r.status,
        ib.event_info          AS input_buffer
FROM    sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE   s.session_id = 123;   -- the blocking SPID from step 1

sys.dm_exec_input_buffer is available from SQL Server 2014 SP2 / 2016 onward. On older versions, use DBCC INPUTBUFFER(123).

Check that:

• It's the packing slip process. The input buffer shows the packing slip procedure, and the host is the TaskMan / utility server.
• The start time matches the long-running packing slip task in Active Background Tasks.
• The parameters match the bad range. Check them in the input buffer if they appear there, and in the task's parameters in Active Background Tasks. In our case, the order range x1004 to x10005 settled it.

Only when all three line up is it safe to go ahead.

3. Kill the SPID

With the system administrator agreeing:

KILL 123;   -- the confirmed blocking SPID

Then watch the rollback:

KILL 123 WITH STATUSONLY;

Killing a session makes SQL Server roll back whatever that session hadn't committed. On a task that's been running for days, the rollback can take a while. Invoicing stays blocked until it finishes, so don't kill it twice or restart services while it's rolling back.

4. Check what was already processed

Depending on how the process commits its work, some of the orders may already have been processed before the kill. Check the orders in the intended range, so nothing is printed or shipped twice.

5. Re-run, then invoice

• Re-run the packing slips with the correct range.
• Run invoicing and confirm it completes.
• Check Active Background Tasks and Background Task History for both tasks, and note what happened, with the SPID, the time and the bad range, on the ticket.

Don't just restart services. It doesn't fix a bad parameter, and if users resubmit the same range, you're back where you started.

Stop it happening again

• Check long runners every morning. A two-minute look at Active Background Tasks for anything RUNNING since yesterday would have caught this on day one, not day four.
• Know your normal run times. Write down roughly how long packing slips, invoicing and the big month-end reports usually take. "Running longer than normal" only means something if you know what normal is.
• Alert on blocking. SQL Server can alert when a session has been blocked for more than a set time. A blocked-for-10-minutes alert on the SyteLine database turns days of silent waiting into an email the same morning.
• Add a range check to the form. For the forms where a mistyped range does real damage, such as packing slips, invoicing and posting utilities, a small customization can warn the user when the range is unusually wide, or when start and end have different lengths, before the task is submitted. It's a small development job compared with days of stopped invoicing.
• Train the pattern, not just the fix. "If one process stops and everything else works, check the queue for a long runner" is worth teaching every key user.

FAQ

Why is SyteLine invoicing stuck when other reports still run?

A common cause is another task, often packing slips or a posting run, holding locks on the same orders and shipments. Look for a long-running task in Active Background Tasks and check its parameters.

How do I find a SyteLine task that's been running too long?

Open Active Background Tasks and look for RUNNING tasks whose start time is much older than the task normally takes.

How do I stop a stuck SyteLine background task?

Not from inside SyteLine. Find the task's SQL Server session (SPID) with the blocking query, confirm from its input buffer, start time and parameters that it's the right task, then have an administrator run KILL on that SPID and wait for the rollback to finish.

Is it safe to kill a SyteLine SPID?

Only after confirming exactly what that session is running. Killing it rolls back its uncommitted work, so check which records it had already processed before re-running.

How can I prevent wrong ranges in SyteLine?

Watch for long runners daily, alert on SQL Server blocking, and add a range check to high-risk forms such as packing slips and invoicing.