I have a situation where it appears as if I've lost data in a single table.
I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the database, for debug and support purposes. Other than the request and response, the logging table also contains an
IDENTITY(1,1) column (which is the primary key) and other various columns.
Over a duration of about 15 minutes, it appears as if 2798 rows have "disappeared" from the table. The primary key simply jumps from 3561297 to 3564095.
During the same time period, I've got debug logs from clients of this API which indicates that something went wrong during the request. My IIS log notes that any request which causes database access during this time period had taken between 30000 and 40000 ms to complete.
The odd thing is, that other tables does not appear to have similar gaps. In fact other tables have had data inserted and updated in them, without issue it appears. So it doesn't appear to be a full system crash.
Through my Google-Fu I've come across the fact that gaps in a
IDENTITY(1,1) key can be caused by a restart of the server -- and in that case the gap is roughly 1000. However, in this case I'm 100% certain that the database has not been restarted, yet I have this gap.
I've tried reproducing this error by inserting rows and adding a
THROW statement in the end, in my test environment to no avail. I've tried to simulate timeouts by both adding a
WAITFOR DELAY and making a
TABLOCKX, HOLDLOCK on the table, while trying to do a lot of inserts through the application. Every database operation is done through stored procedures btw.
I'm quite frankly stumped. Does anyone have any ideas / hints as to why I've got this apparent data loss in one table? Or what can cause a massive gap in the primary key like this?
As far as I can tell I have no transactions in the logging middlewares SQL. And
OFF on the server, and I'm not using
XACT_ABORT in the query. How can a transaction rollback occur, if I do not execute a
BEGIN TRANSACTION? I thought SQL Server only made "auto transactions" if
IMPLICIT_TRANSACTIONS was set to