Resolving Ignition Transaction Group Delayed Inserts

The symptom: an Ignition 8.1 Standard Group fires on its trigger, the Designer shows no error, and nothing lands in the SQL Server table. After several more triggers, the whole backlog appears at once. The group ran for weeks without this. The group is working. The database is not keeping up, and Ignition's store-and-forward (S&F) system is holding the records until it can deliver them.

Skip These Quick Fixes

These are the usual first moves. Each one misses the cause.

• Re-firing the trigger or toggling the group. The group already executed. Every extra trigger just adds another record to the S&F queue.
• Rebuilding the group, tags, or trigger logic. The OPC reads and the trigger are fine. The rows arrive with the correct values, just late. Reworking the trigger changes nothing downstream.
• Restarting the Gateway. Anything still in the S&F memory buffer is at risk if the disk cache is disabled. The database stall is still there after the restart.
• Turning off store-and-forward as the fix. This turns silent delays into immediate, visible failures, which is useful for diagnosis. It does not make the database faster. With S&F off, a record that fails while the database is stalled is not queued for retry.
• Blaming the SSMS refresh. A new SELECT always reads committed data. If the row is not there, it has not been committed yet.

Understand Why Records Wait Silently

With S&F enabled, a transaction group does not write directly to the database. It hands each record to the S&F engine for its database connection, and from the group's point of view the execution succeeded. The engine then tries to write the record to SQL Server. If the connection is faulted, the insert times out, or the connection pool has no free connection, the engine keeps the record in its memory buffer or local disk cache and retries. When the database responds again, the engine flushes the backlog. That flush is the "entire set of records appears" behavior.

No error appears in the Designer because the group never saw one. The failure happened one layer down, in the forwarding engine. The fact that S&F kicked in at all means the database was unavailable or too slow at those moments. That is the real fault.

The pattern also rules out a schema problem. Records rejected for data reasons, such as a type mismatch or a missing column, go to quarantine and never show up by themselves. Records that do show up late point to a connectivity or performance problem.

Find the Bottleneck

1. Check the S&F status page. In the Gateway web page, open Status > Connections > Store & Forward. Trigger the group and watch the memory buffer and disk cache counts for your connection. If the counts climb and drain later, you have confirmed the delay path. If the quarantine count is non-zero, open it and read the error. That is a separate data problem.
2. Check the database connection status. Open Status > Connections > Databases. Note whether the connection flips between Valid and Faulted. Compare active connections with the pool maximum. If the pool is maxed out, inserts are waiting for a free connection.
3. Read the Gateway logs. Filter the logs to the time of a delayed trigger. Look for database connection faults, validation failures, query timeouts, and S&F write errors.
4. Look for blocking and log pressure in SQL Server while a trigger is stuck:

5. Check who else uses the same connection. Tag historian, alarm journal, audit logs, and heavy report or named queries can all share this Ignition connection. Any of them can use up the pool or lock the target table.

Restore Timely Inserts

1. Clear the immediate blocker. Commit or roll back any open transaction holding the table. SSMS query windows and row-edit grids are common offenders. Free log or disk space if that is what is stalling SQL Server.
2. Let the backlog drain. Watch the S&F counts return to zero before you change anything else, so you do not lose queued records.
3. Fix the root cause. Depending on what you found, tune the slow queries sharing the connection, raise the pool maximum within what SQL Server can handle, split historian or reporting traffic onto its own connection, or fix the network or host problem.
4. Decide on S&F behavior for this group. If downstream logic needs the row in the table right away, for example a MES step that queries the row, clear the store-and-forward option in the group's settings. With S&F off, database problems show up as immediate execution errors instead of late delivery. Pair this with the group's failure handshake so the PLC or HMI sees the failure. Keep S&F on if eventual delivery matters more than timing, which is typical for production logging.
5. Protect the queue if you keep S&F. In the Gateway, go to Config > Databases > Store and Forward and confirm the disk cache is enabled for this connection. This lets queued records survive a Gateway restart.

Prove Rows Commit on Time

The group's timestamp column records when the group executed, not when SQL Server committed the row. Add a column that SQL Server fills itself, then measure the difference:

ALTER TABLE dbo.your_table
  ADD db_insert_time DATETIME2 NOT NULL
  CONSTRAINT DF_your_table_insert DEFAULT SYSDATETIME();

SELECT TOP 50 t_stamp, db_insert_time,
       DATEDIFF(ms, t_stamp, db_insert_time) AS lag_ms
FROM dbo.your_table
ORDER BY db_insert_time DESC;

Replace t_stamp with your group's timestamp column name if you renamed it. Also confirm the Gateway and SQL host clocks are in sync, or the lag numbers will be skewed.

1. Fire the trigger several times, spread over a shift.
2. Confirm the lag stays consistently small and never jumps into minute-scale batches.
3. Confirm the S&F memory and disk cache counts stay at or near zero, and quarantine stays empty.
4. Confirm the database connection stays Valid through the historian and reporting peak periods.

Watch for These Repeat Offenders

• Success handshake with S&F on. The group reports success when it hands the record to S&F, not when SQL Server commits it. Do not use the success handshake as proof the row is in the table.
• Version confusion. "v19.1" matches the SQL Server Management Studio release numbering, not the database engine. Check the actual engine version and edition with SELECT @@VERSION;. Edition resource limits and patch level matter for performance.
• Problems that grow over weeks. A setup that ran fine and then slowed down usually points to growth: tables without indexes that other queries scan, a log that is never truncated, or autogrowth stalls. It is rarely an Ignition change.
• Disabling S&F and moving on. Records now fail loudly, but without retry they are lost unless something catches the failure.

FAQ