When did this SQL request yield results?

Question

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

Answer 1

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.

Answer 2

I would create a unique filtered index and track the unique constraint violations. It prevents the wrong data in the first place, and unlike periodically polling the table, you won't miss the occurrence.

The definition would be

CREATE UNIQUE NONCLUSTERED INDEX IX_Orders_StatusPerMachine
ON dbo.Orders(MachineId) 
WHERE (Status = 1)

Then I would use the error_reported Extended event to track the specific error number, which probably will be:

Msg 2601
Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

and grab some additional info like app_name, host_name, tsql_stack etc. to find the root cause.

I blog about debugging errors with the Extended events here
https://straightforwardsql.com/posts/investigating-errors-with-extended-events/