Overlapping / inconsistent records in temporal tables

Question

Hello!

I just noticed that data in many of my temporal / system-versioned tables seems to have got somehow corrupted. I believe it happend after that the server ran out of free disk space last week.

There are now multiple records in many of my system versioned tables, where in the connected history tables there are records with the same primary key values AND system start time values (!), but whereas the system end time values in the system versioned tables are at infinity (max datetime) the system end time values of the most recent record with the same primary key value in the connected history table is the recent date when the database was recovered from the full disk incident. The consequence is that queries like "SELECT * FROM myTemporalTable FOR SYSTEM_TIME AS OF <time before incident> WHERE ID = <ID>" in many cases return two records instead of just one! Only a fraction of all records in these tables are affected.

For background, when the database was recovered from the disk space issue, the database was shrinked, and before that, it was (as far as I can remember) likely also detached for a brief amount of time. I dont think any other maintenance operations were performed on the database. I have run various DBCC CHECK* commands to see if there are more serious issues, but no errors were reported by any of these commands.

Would be grateful for any ideas as to why the history tables have got corrupted like this?

PS. My plan is to fix the problem by identifying the affected records, then to disable system versioning on the tables in order to remove the overlapping records from the history table. Interestingly, and maybe helpful for understanding what may have caused the issue, is that any affected record has the same value of its ROWVERSION field in both the system versioned table and the connected history table.

Thanks.

Answer 1

I would say that this sounds like a bug in my ears. Like outstanding transactions were not rolled forward/backward correctly in the steps you took when you recovered from the space issue. Unfortunately, it sounds like something that would be very difficult to reproduce.

What does "SELECT @@version" report?

Answer 2

Thanks Erland.

"SELECT @@version" reports:

"Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) "

When looking at what records were affected, I don't see anything that would be common to them. Some are records that were added long time ago and not touched since, other records were more recently added. I don't think that the records would have been involved in any transactions at the time of recovery, at least not referenced by any update queries!

Answer 3

Hi @Mats

To delete duplicate records, please refer to this article: Different ways to SQL delete duplicate rows from a SQL Table

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

"SELECT @@version" reports: "Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) "

That's a very outdated version, and you should install Service Pack 3 for SQL 2016 as soon as you can find an maintenance window. I believe there is an CU1 for SP3 as well.

I cannot say for sure that this incident would not have occurred, if you had been on SP3. However, temporal tables were a new feature in SQL 2016, and I am sure that there were quite a few bug fixes in the CUs for SP1 and SP2. So it is not entirely unlikely that you ran into a known issue.

And in general, you should strive to be on a recent CU level, to get access to bug fixes. Installing every CU may not be defensible, but you should apply CUs at least twice a year.