Resolving Ignition Store and Forward Duplicate Key Quarantine

What is the screen telling you when historized tags look frozen?

The operator sees trends go flat. Historized tags stop showing new points, and the Store and Forward status for the history connection shows quarantined data. Deleting the quarantine brings the trends back. Days later they freeze again.

First separate the two layers. Open the Tag Browser and watch the live value of a tag whose trend is flat.

If the live value moves and the trend does not, the tag is fine and the history write is failing. The gateway log shows the failure. It contains a stack trace like this:

java.lang.RuntimeException: java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK__sqlt_dat__BE126DD1975C00AB'.
Cannot insert duplicate key in object 'dbo.sqlt_data_1_2025_01'. The duplicate key value is (63, 1737730046000).
  at ...history.sf.sinks.AbstractDatasourceSink.storeToDatasource
  at ...history.sf.sinks.AggregateSink.storeData
  at ...history.forwarders.ForwarderThread.run
Caused by: java.sql.BatchUpdateException ...
  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch
  at ...tags.history.storage.TagHistoryDatasourceSink$BatchedTagInsert.onComplete
  at ...TagHistoryDatasourceSink.insertTagValues

Read the trace from the bottom up:

1. ForwarderThread pulls a batch of tag history records out of the Store and Forward buffer.
2. TagHistoryDatasourceSink$BatchedTagInsert sends the batch to SQL Server as one JDBC executeBatch call.
3. SQL Server rejects the batch because one row's primary key already exists in the monthly partition table sqlt_data_1_2025_01.
4. The whole batch fails. The engine retries it. Every retry hits the same key, so the engine eventually quarantines the batch.

While the forwarder retries the poisoned batch, newer records queue up behind it in the buffer. No new rows reach the database, so every trend that reads from that datasource flatlines at once. The tags look frozen because the history pipeline is blocked. The live values are still arriving.

Check before moving on: confirm in the gateway log that the first BatchUpdateException lines up in time with the moment the trends went flat.

What row is the duplicate key pointing at?

The standard tag history partition tables use a primary key of tag ID plus timestamp. The timestamp is in epoch milliseconds. In the standard schema these columns are tagid and t_stamp. Decode the evidence value (63, 1737730046000) as follows:

Query the database to see what is already stored for that key and which tag it belongs to:

-- Row already occupying the key
SELECT * FROM dbo.sqlt_data_1_2025_01
WHERE tagid = 63 AND t_stamp = 1737730046000;

-- Tag path behind ID 63 (standard tag-entry table)
SELECT * FROM dbo.sqlth_te WHERE id = 63;

-- Historian drivers writing into this schema
SELECT * FROM dbo.sqlth_drv;

SQL Server reports only the first duplicate it hits. The same batch can hold more collisions, so do not assume tag 63 is the only affected tag.

Check before moving on: record the stored value and quality for the colliding row. Also record the tag path, and whether sqlth_drv lists more than one gateway or provider writing into this database. Those results decide which of the next three sections applies.

Is the history connection configured with a failover datasource?

A common cause of this exact error is a failover datasource configured on the database connection used by the Tag Historian. With connection-level failover, the Store and Forward engine sees only one logical connection. After a primary-to-failover switch and back, the engine cannot tell which database committed which batch. It re-sends records the primary already holds, and the primary rejects them with a primary key violation.

If you need history in two databases, use the Tag History Splitter. It treats each connection as a separate sink with its own acknowledgement.

A clean configuration has these properties: no failover datasource on the historian connection, and one history provider per database connection. On a non-redundant gateway with local tags, that setup rules this cause out.

Check before moving on: open the history connection's edit page and confirm the failover datasource field is empty. On Config > Tags > History, confirm no second provider points at the same connection.

Is something else writing into the same partition tables?

A primary key collision means another writer got there first. The configuration may look clean, with no failover, no redundancy, and history configured on the local gateway. In that case look for a second writer outside the historian settings:

Check before moving on: identify every client that writes into the sqlt_data tables. SQL Server's session or audit views on the history database show this. Only the Tag Historian from this gateway should appear.

Is the insert acknowledgement being lost between gateway and SQL Server?

With a single writer and no failover, the usual mechanism is a lost acknowledgement:

1. The forwarder sends a batch.
2. SQL Server commits it.
3. The confirmation never makes it back to the gateway. The cause can be a network drop, a socket or query timeout, a connection reset from the pool, or a database stalled on blocking or I/O.
4. The gateway marks the batch as failed and sends it again.
5. The rows are already committed, so the re-send violates the primary key.

Duplicate-key failures are also expensive for the database. Each rejected batch is a full round trip plus a rollback. On a congested link or busy SQL Server this adds load and makes further timeouts more likely.

"Local gateway" means the tags are local. The SQL Server can still sit on another host, VM, or network segment. Diagnose the path, not the tag location:

1. In the gateway log, find the entries just before the first Violation of PRIMARY KEY of each occurrence. Timeouts, connection resets, or pool exhaustion messages on the history connection a few seconds earlier confirm a lost acknowledgement.
2. On Status > Connections > Databases, watch the history connection's state and query throughput. Look for faulted or reconnecting periods.
3. On SQL Server, check for blocking, long-running maintenance, and backup or index jobs that overlap the failure times. Heavy disk latency on the data or log volume is another sign.
4. Run a sustained ping between the gateway host and the SQL Server host during the window when failures recur. Look for packet loss or latency spikes.

Check before moving on: each quarantine event should correlate with a specific connection disruption or database stall. Fix that disruption: network path, SQL Server maintenance schedule, or database resources. Otherwise the quarantine will keep coming back.

What should you do with the quarantine and the buffer right now?

Raising the memory buffer size and deleting the quarantine clears the symptom. Neither changes the cause. Know what each action costs:

Deleting the quarantine is the practical short-term workaround when the batch is mostly duplicates. The lost-acknowledgement case fits this: the data is already in the table. Before deleting, export or screenshot the quarantine summary, including record count and time range. That gives you a record of the gap in history.

If a quarantined batch covers a period that must not have a gap, you have one manual recovery option. First confirm the stored rows match the quarantined values. Then remove the colliding rows from the partition and retry the batch. Treat this as a database change with a backup in hand, not routine operation.

Check before moving on: after clearing the quarantine, the Store and Forward status for the history connection shows the buffer draining toward zero. The quarantine count stays at zero.

When should this go to Inductive Automation support?

Escalate if the configuration checks come up clean and quarantines keep recurring. "Clean" here means no failover datasource, no second writer, and no correlated connection disruption. The cause is then either inside the database or a logging source you have not found.

Open a ticket through the Inductive Automation support portal. If a support agreement is in place, support can also tune the Store and Forward engine settings for your write volume. Send these items with the ticket:

1. Gateway logs covering several quarantine events, including the minutes before each first BatchUpdateException.
2. The full stack trace, including the constraint name and duplicate key value.
3. Screenshots of Config > Tags > History and the history database connection settings.
4. Store and Forward engine settings and the quarantine counts and time ranges.
5. Output of the sqlt_data, sqlth_te, and sqlth_drv queries from the duplicate-key section.
6. SQL Server version, whether the database is on the gateway host or remote, and any maintenance or replication jobs on it.

How do you prove the history pipeline is healthy end to end?

1. In the Tag Browser, confirm the live value of the tag behind ID 63 is changing with good quality.
2. Query the current month's partition for that tag ID, ordered by descending t_stamp. The newest timestamp should be within one sample interval of now.
3. On Status > Connections, confirm the history connection is valid. The Store and Forward buffer should hover near zero rather than climb.
4. Confirm the quarantine count is zero. It should stay at zero across the time window where failures used to recur. If the failures were daily, that means at least one full day.
5. Search the gateway log over the same window for Violation of PRIMARY KEY. There should be no matches.
6. Open a trend on the operator screen that uses the affected tags. Confirm the pens extend to the current time with no flat segment since the fix.

FAQ