Sql-server – Missing or lost transaction and no errors to show what went wrong

The reason you are seeing this:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

Is because you have your database option set for AutoClose.

To turn off AutoClose, do this:

alter database YourDatabase
set auto_close off
go

What AutoClose does is shut down the database after the last user process disconnects. And then database automatically "reopens" when the next user connection attempts to connect.

Typically it is best practice to keep AutoClose OFF, because of the obvious latency to spin up the database again. I understand from your question that this is a development environment, but you shouldn't need it there either (and you definitely don't want that in your production environment).

Furthermore DBCC CHECKDB is not actually "firing every 20 - 60 seconds" per the question title, it just appears that way!

Since SQL Server 2005 the dbi_dbccLastKnownGood value (if present) is reported in the error log every time the database is started. So you are just seeing the same historic information presented repeatedly.

This topic is covered in the article Why is SQL Server running DBCC CHECKDB against my databases at Server Startup?

As Remus succinctly puts it, there is no secret feature to rollback a transaction without logging. It is also not possible to rollback a committed transaction.

Your code is broken, not SQL Server.

From the scant details the only scenario I can envisage is the now missing data having been inserted as one long running distributed transaction, someone sighting the data via an uncommitted read and the transaction subsequently being rolled back.

Most likely is an error in the logic or analysis of the failure. Human error and cover-up perhaps even more so.