Database Administration
sql-server scheduled-tasks archive-log
Updated Sat, 10 Sep 2022 08:41:08 GMT

When did this SQL request yield results?


I'm working on an SQL server database, containing orders and machines, executing those orders. Not more than one active order can be assigned to a machine at the same time. In other words: this SQL request can never yield a result:

SELECT MachineId
  FROM Orders
  WHERE (Orders.Status=1)
  GROUP BY MachineId
  HAVING COUNT(Id)>1

I have this SQL request open in a Microsoft SQL Server Management Studio environment, and every time I press F5, I indeed see no results.

However, I am sure that there have been times where that SQL query did yield results, and I'm interested in those times and the results of that query at those times.

Does anybody have an idea on how I can find this out? (The "archive-log" tag is just an idea)

Thanks in advance




Solution

Create a logging table. Change your query to

INSERT LoggingTable(...)
SELECT MachineId, SYSUTCDATETIME()
...

Use SQL Agent to run this query as required. (It's minimum interval is 1 minute.)

The problem is you only see errors that exist for the few milliseconds the query executes every few minutes - a fraction of a percent of the time the system is running. If you add temporal tracking to Orders you can see every change that has happened. You can then run an analysis query over this history to detect errors. History can be copied to a separate archive DB if desired and removed from the operational DB when no longer required, to keep that DB lean.







External Links

External links referenced by this document: