Free SyteLine SQL diagnostic scripts and checklists

1. Find the session blocking a background task

When one SyteLine process stops while small reports keep running, another task with a mistyped parameter range is often holding locks. Step 1 lists blocked requests and who is blocking them. Step 2 shows the blocking session's host, program, login time and input buffer, so you can match it to a task in Active Background Tasks before anyone touches it.

Needs: VIEW SERVER STATE, SQL Server 2014 SP2 or later. Full guide: Why a SyteLine task runs for days.

/*
File: 01_syteline_blocking_spid_check.sql
System: Infor SyteLine 8, 9, 10 (On-Premises SQL Server)
Author: Al Hasib Tamim — Clear Cutover (https://clearcutover.com/)
Full Guide: https://clearcutover.com/blog/syteline-task-running-for-days-wrong-range

Purpose:
When one SyteLine process (such as invoicing) stops while small reports keep running,
another background task with a mistyped parameter range may be holding locks on shared
order/shipment tables.

SAFETY NOTES — READ BEFORE RUNNING:
- These queries are DIAGNOSTIC ONLY. They do not modify data.
- A blocking session may be doing legitimate work. Do not kill it without confirmation.
- A blocking SPID can change or end between Step 1 and Step 2. Re-run Step 1
  immediately before any intervention to confirm the SPID is still the correct one.
- Killing a session can trigger a transaction rollback that takes longer than the
  original task. Monitor with KILL ... WITH STATUSONLY.
- A test database will typically NOT reproduce production blocking behaviour
  (different data volumes, isolated workload, no live users). A clean test run
  is not evidence that there is no production issue.
- Requires VIEW SERVER STATE (on SQL Server 2022+, the narrower
  VIEW SERVER PERFORMANCE STATE is also enough). VIEW DATABASE STATE is not
  sufficient for these server-scoped DMVs on on-premises SQL Server. Azure SQL
  Database and Azure SQL Managed Instance have service-specific requirements —
  confirm against Microsoft's current documentation before relying on this
  script there.
- sys.dm_exec_input_buffer requires SQL Server 2014 SP2 / 2016+. On older versions
  use DBCC INPUTBUFFER(<spid>).

- The blocking_spid in Step 1 is the session DIRECTLY blocking each request.
  In a chain, that session may itself be blocked; follow blocking_spid until
  you reach a session that is not blocked (the head blocker).
- The head blocker is often an idle session holding an open transaction. It
  has no row in sys.dm_exec_requests, so start_time and status in Step 2 will
  be NULL. Use login_time, last_request_end_time, and the input buffer instead.

Step 1: Identify active blocking sessions.
Step 2: Inspect the blocking SPID's input buffer, login time, and host BEFORE touching it.
*/

-- =============================================================================
-- STEP 1: Find blocked requests and the blocking_session_id (SPID)
-- =============================================================================
SELECT
    r.session_id           AS blocked_spid,
    r.blocking_session_id  AS blocking_spid,
    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
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- =============================================================================
-- STEP 2: Confirm the blocking session's identity and parameters FIRST
-- Replace 123 below with the blocking_spid returned in Step 1.
-- Re-run Step 1 to confirm the SPID is still the same session before proceeding.
-- Verify that:
--   1. The input_buffer shows the expected background procedure (e.g., packing slip)
--   2. The host_name matches your TaskMan / utility server
--   3. The start_time (or login_time if the session is idle) matches the
--      RUNNING task in SyteLine Active Background Tasks
-- =============================================================================
DECLARE @BlockingSPID INT = 123; -- Replace with confirmed blocking_spid

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.login_time,
    s.last_request_end_time,
    s.open_transaction_count,
    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 = @BlockingSPID;

/*
STEP 3 (ADMIN ONLY — DO NOT RUN UNTIL STEP 2 IS VERIFIED AND STEP 1 HAS BEEN
RE-RUN TO CONFIRM THE SPID IS STILL THE SAME SESSION):

    KILL 123;
    KILL 123 WITH STATUSONLY;   -- Watch rollback progress; do not restart TaskMan
                                -- during rollback.

Killing a session that is not actually the intended blocker can break a running
application transaction. Confirm twice. Prefer cancelling the task from SyteLine
Active Background Tasks where possible.
*/

Download 01_syteline_blocking_spid_check.sql · View on GitHub

2. Rank slow report stored procedures

Lists the 25 slowest cached report procedures in the current database by average elapsed time, CPU and logical reads, so you tune the report that actually costs the most. It reads the plan cache, so it only sees procedures that have run since the last restart or recompile.

Needs: VIEW SERVER STATE. Full guide: Why your SyteLine report is slow.

/*
 * File: 02_syteline_slow_report_tuning_check.sql
 * System: Infor SyteLine / CloudSuite Industrial (SQL Server & SSRS)
 * Author: Al Hasib Tamim — Clear Cutover (https://clearcutover.com/)
 * Full Guide: https://clearcutover.com/blog/syteline-slow-report-sql-tuning
 * Related:    https://clearcutover.com/blog/syteline-background-task-definitions
 *
 * Purpose:
 *   Section 1 — list the slowest CACHED stored procedures in the CURRENT database
 *               by average elapsed time and logical reads. Useful for ranking
 *               candidate report procedures (typically RPT*Sp) worth reviewing.
 *   Section 2 — a commented-out manual testing template for running a specific
 *               procedure in SSMS with SET STATISTICS IO/TIME ON, so you can
 *               inspect the actual plan for cursors, scalar UDFs, non-SARGable
 *               predicates, and missing site_ref filters.
 *
 * Limitations:
 *   - This script does NOT automatically detect non-SARGable predicates, missing
 *     site_ref joins, or cursor bottlenecks. That analysis is manual (Section 2).
 *   - sys.dm_exec_procedure_stats reads the plan cache. Rows disappear when plans
 *     are evicted (memory pressure, recompile, restart, statistics update).
 *   - Statistics are per-database and per-cached-plan, not a historical record.
 *     The database_id = DB_ID() filter means procedures cached in other
 *     databases will not appear.
 *   - Permission (on-premises SQL Server): VIEW SERVER STATE (on SQL Server
 *     2022+, the narrower VIEW SERVER PERFORMANCE STATE is also enough).
 *     VIEW DATABASE STATE is not sufficient for this DMV on on-premises
 *     SQL Server. Azure SQL Database and Azure SQL Managed Instance have
 *     service-specific requirements — confirm against Microsoft's current
 *     documentation before relying on this script there.
 *   - If OBJECT_NAME() returns NULL, check metadata visibility (VIEW DEFINITION)
 *     and confirm the procedure exists in the database being queried. Neither
 *     replaces the DMV permission above.
 */

-- =============================================================================
-- 1. List top 25 slowest cached stored procedures in the current database
-- Note: Standard SyteLine report procedures typically match '%Rpt%Sp%', but custom
-- report procedures may use site-specific prefixes. Comment out the LIKE filter
-- to inspect all cached stored procedures in the database.
-- =============================================================================
SELECT TOP 25
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,
    CAST(ps.total_elapsed_time / 1000000.0 / NULLIF(ps.execution_count, 0) AS DECIMAL(12, 2)) AS avg_elapsed_seconds,
    CAST(ps.max_elapsed_time   / 1000000.0 AS DECIMAL(12, 2)) AS max_elapsed_seconds,
    CAST(ps.total_worker_time  / 1000000.0 / NULLIF(ps.execution_count, 0) AS DECIMAL(12, 2)) AS avg_cpu_seconds,
    ps.total_logical_reads / NULLIF(ps.execution_count, 0) AS avg_logical_reads,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
  AND ps.execution_count > 0
  AND OBJECT_NAME(ps.object_id, ps.database_id) LIKE '%Rpt%Sp%'  -- comment out to include non-Rpt names
ORDER BY avg_elapsed_seconds DESC;

-- =============================================================================
-- 2. Manual diagnostic template — run in SSMS against a TEST database
--    with parameters copied from Background Task History.
--
-- What to look for in the actual execution plan:
--   - Cursors looping row-by-row over invoices or open items
--     (replace with set-based CASE aging buckets where possible)
--   - Scalar UDFs called inside SELECT or WHERE clauses
--   - Non-SARGable date conversions
--     (e.g., CONVERT(VARCHAR, inv_date) = '2026-01-01'
--      instead of inv_date >= '2026-01-01' AND inv_date < '2026-02-01')
--   - Missing site_ref predicates on _mst_all views
--   - Key lookups / scans on large order / invoice tables
-- =============================================================================
/*
SET STATISTICS IO, TIME ON;
-- EXEC dbo.<ReportProcedureSp> ...   (find the name in Background Task
--                                    Definitions; paste parameters from
--                                    Background Task History)
SET STATISTICS IO, TIME OFF;
*/

Download 02_syteline_slow_report_tuning_check.sql · View on GitHub

3. Data health check before MRP or migration

Three queries that catch the master-data problems that quietly break MRP and migrations: active items with no cost, active items with no lead time, and the same customer name under different customer numbers.

Needs: SELECT on item_mst and custaddr_mst. Full guide: SyteLine upgrade support.

/*
 * File: 03_syteline_data_health_check_readonly.sql
 * System: Infor SyteLine / CloudSuite Industrial
 * Author: Al Hasib Tamim — Clear Cutover (https://clearcutover.com/)
 * Service Reference: https://clearcutover.com/syteline
 *
 * Purpose:
 *   Starter read-only SQL checks to catch master data exceptions before MRP
 *   runs, financial costing rolls, or ERP migration extraction cycles.
 *
 * BEFORE RUNNING — VERIFY FOR YOUR INSTALLATION:
 *   - Table names: this script assumes standard `_mst` master tables
 *     (item_mst, custaddr_mst). Some environments expose site-filtered views
 *     (`item`, `custaddr`) instead, or use `_mst_all` variants. Confirm with
 *     SELECT name FROM sys.objects WHERE name LIKE '%item%' before running.
 *   - Column names: `unit_cost`, `cur_u_cost`, `lead_time`, `paper_time`,
 *     `p_m_t_code`, `stat`, `cust_seq`, `cust_num` are standard but can
 *     differ across versions and customizations. Verify in your schema.
 *   - Site scope: `_mst` tables usually contain all sites. Uncomment the
 *     `site_ref` line in each query to scope to a single site.
 *
 * All queries are read-only. Requires SELECT permission on the referenced
 * tables/views in the target database.
 */

-- -----------------------------------------------------------------------------
-- 1. Active items with zero or NULL unit cost
--    If you also want to restrict by item category (manufactured / purchased),
--    add a filter after verifying the valid p_m_t_code values in your
--    installation (they are not universal across versions):
--        AND p_m_t_code IN ('M','P')   -- example only
--    The query as shipped does NOT restrict by p_m_t_code.
-- -----------------------------------------------------------------------------
SELECT
    item,
    description,
    p_m_t_code,
    stat,
    unit_cost,
    cur_u_cost
FROM dbo.item_mst
WHERE stat = 'A'
  AND ISNULL(unit_cost, 0) = 0
  -- AND site_ref = '<your_site>'   -- uncomment to scope to one site
;

-- -----------------------------------------------------------------------------
-- 2. Active items with missing or zero lead time
-- -----------------------------------------------------------------------------
SELECT
    item,
    description,
    p_m_t_code,
    lead_time,
    paper_time
FROM dbo.item_mst
WHERE stat = 'A'
  AND ISNULL(lead_time, 0) <= 0
  -- AND site_ref = '<your_site>'   -- uncomment to scope to one site
;

-- -----------------------------------------------------------------------------
-- 3. Duplicate customer names across distinct customer numbers
--    (useful before migration extraction)
-- Checks across all custaddr rows (both cust_seq = 0 primary bill-to
-- and cust_seq > 0 ship-to) and counts how many matching records are
-- primary bill-to accounts (cust_seq = 0).
-- -----------------------------------------------------------------------------
SELECT
    LTRIM(RTRIM(name)) AS normalized_customer_name,
    COUNT(DISTINCT cust_num) AS customer_count,
    COUNT(DISTINCT CASE WHEN cust_seq = 0 THEN cust_num END) AS bill_to_customer_count,
    MIN(cust_num) AS first_cust_num,
    MAX(cust_num) AS last_cust_num
FROM dbo.custaddr_mst
WHERE LTRIM(RTRIM(ISNULL(name, ''))) <> ''
GROUP BY LTRIM(RTRIM(name))
HAVING COUNT(DISTINCT cust_num) > 1
ORDER BY customer_count DESC;

Download 03_syteline_data_health_check_readonly.sql · View on GitHub

Checklists: TaskMan and Epicor Kinetic

• SyteLine report stuck in WAITING or no PDF: a five-step check, from Background Task History through TaskMan, the output folder and the parameter range. Full guide: SyteLine report stuck in WAITING.
• Epicor Kinetic BAQ, SSRS and DMT checklist: the usual causes of duplicated or missing BAQ rows, post-upgrade "Invalid column name" errors, and CSV checks to run before a DMT load. More on Epicor Kinetic subcontracting.

Before you run anything

• Test copy first. Table names, columns and site scope vary between SyteLine versions and customizations. Check each script on a test database before production.
• Read-only by design. Nothing here changes data. The KILL in the blocking script is commented out on purpose: confirm the session twice, and cancel the task from Active Background Tasks where you can.
• Fix data through the application. Correct bad master data through SyteLine forms, IDOs or a validated load, not with raw UPDATE statements.
• On-premises only. Multi-tenant CloudSuite Industrial doesn't allow direct SQL. The checklists still apply.

FAQ

Are these scripts safe to run on a production SyteLine database?

They only read data. Every script is SELECT-only, and the KILL command in the blocking script is commented out. Even so, run them on a test copy first to check table names, permissions and site scope for your version.

What permissions do the scripts need?

The blocking and slow-report scripts read server-level DMVs, so the login needs VIEW SERVER STATE (on SQL Server 2022 and later, VIEW SERVER PERFORMANCE STATE is also enough). The data health check only needs SELECT on item_mst and custaddr_mst.

Do they work on multi-tenant CloudSuite Industrial?

No. Multi-tenant cloud doesn't allow direct SQL access. The TaskMan and Epicor Kinetic checklists still apply, because they work through the application rather than SQL.

Can I use them in my own work or training material?

Yes. They're MIT licensed, so you can use, change and share them. A link back to this page is appreciated.