Addressing SmartConnect Error Caused by Excessive TraceHeader Records.

When attempting to log in to SmartConnect On-Premise, you may encounter the following error:

could not execute native bulk manipulation query: Delete from TraceHeader Where StartDate < (GETUTCDATE() – 30)[SQL: Delete from TraceHeader Where StartDate < (GETUTCDATE() – 30)]

This issue can occur when you have excessive TraceHeader records. Usually, this means a large number of historical integration log records have accumulated in the TraceHeader and TraceLine tables.

Causes – Excessive TraceHeader Records

SmartConnect stores integration execution and trace information in the TraceHeader and TraceLine tables if integration logging is enabled under the options tab.

In SmartConnect 21, the default Integration Logs to Keep setting is 30 days for bulk delete. When a user logs in, SmartConnect automatically attempts to remove log records older than 30 days.

In older versions, such as SmartConnect 2018, the default log retention was Forever. If the default setting was never changed, a large number of historical records could accumulate over time, potentially resulting in millions of records.

When SmartConnect 21 attempts to remove these older records, “SmartConnect Config Details” (SmartConnect Configuration) has an 180-second command timeout. If the delete operation cannot complete within that period because of the volume of data, SmartConnect displays the error. 

Does this prevent login?

No. This error does not prevent you from logging in to SmartConnect. You can click OK and continue using SmartConnect.
However, the message may appear again the next time you log in because SmartConnect will continue attempting to remove records.
Large volumes of trace records can also negatively impact SmartConnect performance.

Solution

The recommended solution is to manually clean up the older trace records directly from the SmartConnect database using SQL Server Management Studio (SSMS).
Running the cleanup directly in SSMS allows you to manage the deletion separately from SmartConnect’s login process and avoid the application’s command timeout.

This should be a one-time cleanup, and SmartConnect will continue to check for and remove logs each time you log in. However, because the volume of records will now be significantly lower, the cleanup query should complete within the command timeout, and it should no longer generate the error.

This will also improve the SmartConnect login experience because it will no longer spend several minutes attempting to process a large volume of historical trace records before allowing you to continue.

Step 1: Check the Number of Trace Records

Before deleting any records, determine how many records are older than the desired retention period and run the following queries against the SmartConnect database.

declare @D as numeric(2,0)
set @D = 30

select count(l.TraceLineId) as [count] from TraceLine l with (nolock) left join TraceHeader h with (nolock) on l.TraceHeaderId = h.TraceHeaderId where h.StartDate < GETUTCDATE()- @D 

select count(h.TraceHeaderId) as [count] from TraceHeader as h with (nolock) where h.StartDate < GETUTCDATE()- @D

Step 2: Delete the Trace Records

After confirming the record counts and ensuring that it is safe to proceed, run in the same order: likely TraceLine first and TraceHeader next.

Note: Replace 30 with the same value you used and tested in Step 1.

declare @D as numeric(2,0)
set @D = 30

delete l from TraceLine l with (nolock) left join TraceHeader h with (nolock) on l.TraceHeaderId = h.TraceHeaderId where h.StartDate < GETUTCDATE()- @D 

delete h from TraceHeader as h with (nolock) where h.StartDate < GETUTCDATE()- @D

Deleting the TraceLine records first helps ensure that the related detail records are removed before the corresponding TraceHeader records.

Verify the Cleanup

After the deletion is complete, run the queries from Step 1 again.
The returned counts should confirm that the older trace records have been removed.
You can then log out and log back in to SmartConnect to confirm that the timeout error no longer occurs.

Preventing the Issue in the Future

SmartConnect 21 automatically removes integration logs older than the retention period. The default setting is 30 days.

You can review or change the retention period under: Integration → Options → Integration Logs to Keep from 0 up to 15 – that’s per integration.

If your integrations generate a high volume of log records within 30 days, and it requires more than 180 seconds to clean up the trace records, you can increase the command timeout to 600 seconds (i.e., 10 minutes) under the eOne Config Details window and try for it.

Summary

The error occurs because SmartConnect is attempting to automatically remove trace records, but SmartConnect config settings cannot complete the operation within the configured command timeout.

Manually cleaning up the older records is generally a one-time corrective action. Once the large backlog has been removed, SmartConnect should be able to perform its normal log cleanup during login without exceeding the timeout.

Maintaining an appropriate Integration Logs to Keep setting can also help prevent large volumes of trace records from accumulating again, or a command timeout change may be needed.

Have a question? Please reach out to us at support@eonesolutions.com.