Backup table gets extra ID from source table after SELECT INTO, no concurrent DML, RCSI enabled

旭姣 张 0 Reputation points
2026-10-28T02:58:44.1533333+00:00 
SQL Server 2016 RTM (13.0.1601.5), RCSI enabled.

When doing `INSERT INTO backup_table SELECT * FROM source_table`, the backup table contains an extra data that no longer exists in the source table.
I have confirmed:
- No open transactions at the time.
- No concurrent INSERT/DELETE from other sessions during the full scan.
- Neither normal query nor NOLOCK hint can retrieve this ID from source table now.

I suspect this is a ghost record cleanup bug of RCSI full scan in SQL Server 2016 RTM.
Are there any known bugs matching this symptom? What diagnostics should I collect?
SQL Server
SQL Server

A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 138.5K Reputation points MVP Volunteer Moderator
    2026-10-28T08:06:01.8966667+00:00

    As you may know, SQL 2016 is out of extended support, and if you are running the RTM version you are even more behind. You could at least download and install Service Pack 3. Even better to upgrade to a supported version like SQL 2025. This could very well be due to a bug that has been fixed. But you could also run DBCC CHECKDB to see if there is any corruption in the database.

    For troubleshooting, well,

    • Is the situation repeatable at all?
    • If you restart SQL Server, does it still occur?
    • If you disable RCSI, does it still occur?
    • If you restore a copy of the database on an instance running a newer version of SQL Server, does it still exhibit?

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.