IMD SolutionComputing LibraryShop main store

Troubleshooting

Trace a SQL log error after a historian migration

Edge computing and IIoT Published 6 min read

Short answer

Run a SQL Server Profiler trace against the Runtime database, capture the error event with its query text and user context, and match its timestamp to the log entry within 5 seconds. Live data collection and the SQL message are separate observations; the trace tells you which request failed and whether the migration is the suspect.

On this page
  1. Is the SQL error caused by the migration or only exposed by it?
  2. What does the Profiler trace need to capture?
  3. How long should the trace run before it is conclusive?
  4. How do you match a trace event to the log entry?
  5. What does the captured query tell you to fix?
  6. When do you reopen the migration itself?
  7. Questions asked

A SQL message that shows up in the historian log after an in-place version migration is an unidentified failing database request, not a verdict on the migration. Identify it with a SQL Server Profiler trace against the Runtime database that captures the error event together with the query text and user context, run it until the message recurs, then match the event to the log entry by timestamp. Collection can keep working while one specific request fails; those are two separate observations, and only the trace connects them. What follows is the trace from setup to the decision it supports: fix the query, restore a mapping, or reopen the migration.

Is the SQL error caused by the migration or only exposed by it? #

Treat the message as an unidentified failing request until the trace proves otherwise: an upgrade that restores objects and remaps logins can break a query, but it can equally make visible a query that was already structured incorrectly. The log entry itself names neither the request, nor the login that sent it, nor the database it hit. An upgrade is a plausible trigger — documented historian migration procedures restore the Runtime database in SQL Server Management Studio by right-clicking Databases and choosing Restore Database, and a step that half-completed leaves missing objects behind — but plausibility is not identification. A query that was wrong before the upgrade can start failing, or start being logged, the moment a new historian version changes how it calls the database. The trace decides between the two hypotheses, and neither gets acted on before that.

What does the Profiler trace need to capture? #

Capture the error events and the query events that carry the statement text, filtered to the Runtime database, with the login name and the event timestamp displayed — that combination is the minimum that identifies the failing request and who sent it. SQL Server Profiler is the graphical interface to SQL Trace, so it manages the trace as a client of the instance and does not have to run on the database host itself. Set it up like this:

  1. Connect Profiler to the SQL instance hosting the historian databases and start a new trace.
  2. In the Trace Properties window, restrict the trace to the Runtime database.
  3. Select the error events and the query events that include the statement text, and show the login name and the event timestamp alongside them.
  4. Choose Save to File rather than a table, so the trace does not add writes to the store you are diagnosing.
  5. Run the tool from another workstation when the database host is short on headroom, pointing it at the same instance.

A trace that omits the error events cannot show the failure, and one that omits the query events cannot show what failed. Both belong in the same capture, filtered to the one database, or the evidence never assembles.

How long should the trace run before it is conclusive? #

Run the trace for at least five recurrences of the log message: with an entry every 30 s that is 150 s of capture, and an error that appears once a day needs the trace scheduled across that daily window. Five recurrences matter because a single captured error can be a rarer fault that happens to land inside your window; five identical failures on the same request, login and object form a pattern. One caution belongs here: a trace adds work to the SQL host, so scope it to the Runtime database, write it to file, and stop it once the fifth recurrence is captured — do not leave one running overnight against a full event set on the whole instance.

How do you match a trace event to the log entry? #

Match the trace event's timestamp to the log entry inside a ±5 s window: anything that close is the same event, because hosts on a plant network disciplined by NTP sit milliseconds apart and log writers add only small delays of their own. Illustrative example, with numbers you would substitute from your own systems: the historian log records the SQL message at 09:14:32, and the trace holds one error event on the Runtime database timestamped 09:14:31.8. The difference is 09:14:32.0 − 09:14:31.8 = 0.2 s, comfortably inside the window, so the log line and the trace event are the same occurrence. The trace shows that request repeating every 30 s, so 86,400 s ÷ 30 s = 2,880 events per day; a log that accumulated 2,880 such lines in the last 24 h confirms there is one error source, not two. That closes the identification. If no event lands inside the window while the log line keeps appearing, the failure is not reaching this database — rescope the trace rather than drawing a conclusion.

What does the captured query tell you to fix? #

Read the query text and the login together: a request a person or reporting tool sent gets fixed at its source, a request the historian's own service login sent against a missing Runtime object is a migration defect, and a request under a mismatched login is a mapping loss.

What the trace shows What it means Decision
Ad hoc or report-shaped query text; login belongs to a person or reporting tool The request is broken, not the migration Fix the query where it lives; the upgrade only made it visible
Historian service login failing on an object missing from the Runtime database A migration step did not finish Re-run the documented step that creates that object, then confirm the error stops
Request runs under an unexpected or unmapped login Login mapping was lost in the move Restore the mapping the migration procedure defines
No error lands in the trace while the log line continues Wrong scope, or the failure never reaches the engine Widen the trace to the instance and confirm which database the log names

While the trace is open, group the results by Duration, CPU, Reads or Writes to see which statements carry the load on this database; if the failing request is also the most expensive one, it earns the first fix.

When do you reopen the migration itself? #

Reopen it only when the trace shows the historian's own service login failing against Runtime objects that the documented procedure should have created — that is the one trace result that implicates the upgrade rather than a caller. Re-run the specific step for that object, then repeat the capture to confirm the error is gone. When the request instead runs under a login you did not expect, compare it against what the Historian Runtime migration script's SQL users mean before changing anything, because the migration script defines which SQL users the historian expects and an unmapped login is restored rather than debugged. And if the in-place upgrade path keeps failing, moving the data to a different platform is a different job with different steps — see how to move Wonderware Historian data into Canary Historian, or moving DeltaV Continuous Historian data into a PI system, before committing to either path.

Questions asked

Does data still arriving in the historian prove the SQL error is harmless?

It proves the write path collection uses is working, nothing more. The failing request can belong to retrieval, reporting, replication or a secondary store, and those can degrade quietly while trends keep updating. The trace shows which path the failing request belongs to, and that is the only basis for calling the error harmless or not.

What if the trace captures nothing while the log message keeps appearing?

Then the failure is not reaching the database engine as a query. Connection and login failures happen before a request executes and still surface as SQL messages in application logs, so widen the trace from the Runtime database to the whole instance, and confirm which instance the historian is actually configured to use. If the capture stays empty, the message originates client-side.

Can the captured failure be reproduced safely later?

Yes. Profiler can replay a saved trace, so a file captured during the error can be replayed against a restored copy of the Runtime database on a test instance. That reproduces the failure without touching production data, which is the safe way to test a fix for the query before it goes live.

Should the migration be re-run while the error is unidentified?

No. Re-running a documented migration over an unidentified error destroys your before-and-after comparison: if the message stops, you will not know which step fixed it, and if it stays, you have doubled the variables. Capture the trace first, identify the request, then re-run only the specific step the evidence points to.

Keep reading

  • Troubleshooting

    Historian reads slow in Excel after adding tags

    Run one timed test before touching anything: read one old tag and one newly added tag over the same range in the historian's built-in client, then in the Excel macro. Fast in the client but slow in Excel means the macro's request pattern is the fault; the new tag slow in both means you run the historian's validate action and then confirm the SQL maintenance plan actually targets the historian database.

    Edge computing and IIoT

  • Explainer

    What the Historian Runtime migration script's SQL users mean

    The script reconnects the six restored Runtime database users (wwAdmin, wwUser, wwPower, aaAdmin, aaUser, aaPower) to destination server logins and assigns database ownership to the aadbo login. aadbo is a named login the procedure expects, not your current username: run the line as written, create any missing logins through the release's configuration procedure, and verify with the account the Historian service uses.

    Edge computing and IIoT

  • Explainer

    Do you need a historian or just an OPC tag server?

    No. An OPC tag server (OPC DA) supplies the current value, quality and timestamp of each item and stores nothing, so monthly figures need a historian or a SQL logger collecting from that same server. Keep a reporting client like ActiveFactory only if you also run the historian it expects.

    Edge computing and IIoT