Troubleshooting “Execution Timeout Expired” and “Query Timeout Expired” Errors Using SSMS

When Acctivate users attempt to add, edit, or save transactions, such as Sales Orders, Receipts, Invoices, or Inventory updates, they may occasionally receive one of the following messages:

• Execution Timeout Expired
• Query Timeout Expired

These symptoms are commonly caused by blocking or an escalated lock inside the Acctivate database which is powered by Microsoft SQL Server. This guide explains how to use Microsoft SQL Server Management Studio (SSMS) to identify and troubleshoot the blocking process that is responsible for the timeout errors.

Why This Happens

Acctivate uses SQL Server to process every transaction. If one SQL session holds a lock on a table for too long - because of a long-running transaction, report, sync, or other process - other sessions must wait. If the wait exceeds the command timeout, the user receives a timeout error.

The most common underlying cause is:

• A table-level escalated lock
• A user task or SQL job that is stuck or running longer than normal

• A custom trigger or process keeping records locked

Solution

When users receive Execution Timeout Expired or Query Timeout Expired errors in Acctivate, the root cause is often SQL blocking or an escalated lock. By tracing the BlkBy chain in SSMS and identifying the head-blocking SPID, you can quickly determine:

• Which process is causing the lock
• Which workstation/user is running it
• Whether to let it complete or terminate it

Step-by-Step Troubleshooting

Your goal is to identify the head-blocking SPID, determine what it is doing, and decide whether it should be allowed to finish or manually terminated.

1. Connect to the Acctivate Database Server

You can download and install Microsoft SQL Server Management Studio (SSMS) if you don't already have it.

1. Launch SQL Server Management Studio.

2. Connect to the SQL Server instance hosting the Acctivate database (e.g., SERVERNAME\ACCTIVATE or similar).

3. You can probably connect using Windows Authentication if you're on the database server. Otherwise, you'll need to obtain your database server credentials.

2. Identify the Server Process ID (SPID) that's blocking us

You can use either method. Both will show you which SQL sessions are being blocked and by whom.

Option A: Using Activity Monitor

1. In Object Explorer, right-click the Server Name → Activity Monitor.
2. Expand the Processes pane.
3. Ensure the following columns are visible:
   1. Blocked By
   2. ID (SPID)
   3. Host Name
   4. Login
4. Look for sessions where: Blocked By is not empty or not 0. These represent Acctivate sessions waiting for another SPID to release its loc

Option B: Using sp_who2

1. Click New Query.
2. Run: EXEC sp_who2
3. Look for rows where BlkBy contains a number instead of 0 or NULL

Example:

This means SPID 60 is blocked by SPID 55.

3. Trace the Blocking Chain to Find the Head Blocker

Follow the blocking path until you find the SPID that is blocking others but not blocked itself:

1. Start with any SPID showing a non-zero BlkBy.

2. Look up the SPID listed in BlkBy.

3. Check that SPID’s BlkBy value.

4. Continue until you reach a SPID with BlkBy = 0 (or blank).

This is the head-blocking SPID - the one causing the timeout errors in Acctivate.

4. Determine What the Blocking SPID Is Doing

Once you know the head-blocking SPID, identify the activity behind it.

Option A: Using Activity Monitor

1. Locate the SPID in Processes.
2. Review:
   1. Host Name – workstation running the query
   2. Login – user identity
   3. Application – Acctivate or another app
3. Right-click → Details to view the current SQL command.

Option B: Using DBCC INPUTBUFFER

Run the following command in SSMS, replacing <SPID> with the SPID number.

 DBCC INPUTBUFFER(<SPID>)

Option C: Using Dynamic Management Views (More Detailed)

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    SUBSTRING(t.text,
        (r.statement_start_offset/2) + 1,
        ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset
          END - r.statement_start_offset)/2) + 1) AS CurrentStatement,
    t.text AS FullBatchText
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = <SPID>

5. Decide the Correct Action

✔ Allow It to Finish

Recommended when:

1. It’s a legitimate Acctivate task (e.g., posting, sync, import)
2. It is progressing or expected to complete normally

✔ Contact the User

If the SPID corresponds to a workstation:

1. Ask the user what they were doing (running a report, leaving a window open, etc.)
2. Ask them to close or cancel the process

⚠ Kill the SPID (Last Resort Only)

If the blocking is severe and urgent:

6. Gather Information for Acctivate Support

If the blocking is repeatable or caused by a consistent procedure, collect the following information and share with the Acctivate support team:

• Host name and login
• The SQL statement (from Activity Monitor or INPUTBUFFER)
• Screen captures of Activity Monitor or sp_who2
• Description of where the timeout occurred in Acctivate
• Any additional information from the user(s)