All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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:
Cannot insert duplicate key row in object ‘%.*ls’ with unique
index ‘%.*ls’. The duplicate key value is %ls.
and grab some additional info like
tsql_stack etc. to find the root cause.
I blog about debugging errors with the Extended events here
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂