DBCC CheckDB and CheckTable doesn’t check In-Memory OLTP.

Special thanks to my friend Theresa Iserman for introducing me to Jos de Bruijn the Senior Program Manager for the In Memory OLTP (Hekaton) project to help get my questions answered at PASS Summit.

So, the Hekaton – In Memory OLTP tables are perhaps one of the most amazing performance improvements to SQL OLTP in a long time. The way that they are managing data with no latches, locks or spinlocks is awesome, and the performance gains are great.

However in a recent session at Pass Summit 2016 with Bob Ward, I followed up with a question on CheckDB and In Memory OLTP tables. Since the data for these are not store in the normal SQL Server data files or even in regular pages. Bob confirmed that CheckDB (and CheckTable) does not check the data associated with the In Memory OLTP tables. I even confirmed this from Books Online in a post called “Transact SQL Constructs Not Supported by In-Memory OLTP” which stated the following:

• DBCC CHECKDB skips the memory-optimized tables in the database.
• DBCC CHECKTABLE will fail for memory-optimized tables.

So I started to get a bit worried, knowing how important CheckDB is for data integrity, how can I make use of the In Memory OLTP tables if they don’t get checked. I even found a Microsoft connect post with people requesting support in CheckDB for these tables.

Today (SQL 2014 & 2016), CHECKDB skips in-memory OLTP tables. It needs to check the checksums on those tables so that we can find corruption before we find it the hard way.

Scenario: weekly full backups on Saturday, and log backups every X minutes. Corruption strikes on Monday, and we find out when we have an accidental reboot on Friday. The database is now completely down, and we have to wait until we restore the database from full + all transaction logs since.

Link to the Connect Item:  https://connect.microsoft.com/SQLServer/feedback/details/2564709/dbcc-checkdb-needs-to-check-the-checksums-on-in-memory-oltp-hekaton-tables

The Microsoft Connect post made me even more concerned about using the In Memory OLTP.

So I did some experimentation and created a database with In Memory OLTP, and added corruption to one of the files. I then restarted SQL Server and … Kaboom… Recovery pending.

Although it would be nice to have CheckDB check like the connect item above requests, Jos pointed out that that it is not entirely needed, and we can know if we have corruption by doing backups…

• You don’t need it because:
  • All the data in your memory optimized tables is either in the log or a checkpoint files (data and delta files).
  • The checkpoint files have checksums so that when they are loaded you can tell if they are corrupt.
  • When full or differential backups are run, the checkpoint files are backed up and the checksums are checked.
  • If your database is too big to back up daily, you can always backup to the null disk to simulate the backup without creating a file.
  • If there is corruption in the In Memory OLTP Checkpoint (data and diff) files, it will cause the backup to fail.
  • When you see failed backups, you need to research and determine why they have failed. (Should be part of the regular process).

Really the only way to know if you have corruption in your In Memory OLTP tables is to back them up, with a full or differential backup.

So in an environment that you only run full backups weekly, but you want to check for corruption in your In Memory OLTP files, here are some options.

1. Full backups daily and chase down problems if the backups fail. Probably not feasible since in this example we are already only running full backups weekly for a reason.
2. Run differential backups daily. Not a bad idea, but may take up significantly more disk space than you have available.
3. Backup to the NUL disk daily, and use this as a surrogate CheckDB tool.

So once corruption is introduced to your In Memory OLTP data checkpoint files, when you attempt to back up you will get the following message.

Processed 336 pages for database ‘HekatonTest’, file ‘HekatonTest_data’ on file 1.
Location: “d:\\b\\s1\\sources\\sql\\ntdbms\\hekaton\\sqlhost\\sqlmin\\hkhostbackup.cpp”:2973
Expression: !bckFileInfo->Processed
SPID: 56
Process ID: 3664
Msg 3013, Level 16, State 1, Line 65
BACKUP DATABASE is terminating abnormally.
Msg 3624, Level 20, State 1, Line 65
A system assertion check has failed. Check the SQL Server error log for details.
Typically, an assertion failure is caused by a software bug or data corruption. To
check for database corruption, consider running DBCC CHECKDB. If you agreed to send
dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update
might be available from Microsoft in the latest Service Pack or in a Hotfix from
Technical Support.

Which is sort of comical in the recommendation to run DBCC CHECKDB, since we have been told that this will not be checked by DBCC CHECKDB.  But lets humor the system and run DBCC CHECKDB.

Next we try DBCC CheckTable on an in memory optimized OLTP table.

Msg 5296, Level 16, State 1, Line 86
Object ID 565577053 (object ‘InMemoryHekatonTable’): The operation is not supported with
memory optimized tables. This object has been skipped and will not be processed

Ok, so what about msdb.dbo.suspect_pages, well nothing shows up there either.

How about a differential backup… well the differential backup shows the same error messages at the full backup.

And then finally backup to the nul device.

So if you want to check your In Memory OLTP (Hekaton) tables for corruption the only way to do it is to back them up and look for error messages.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!