location:     AppendOnlyVerStoreMgr.cpp:776
Expression:   0
SPID:         64
Process ID:   4912
Description:  Cannot locate version record and page is not allocated. Status = 3
Msg 3624, Level 20, State 1, Line 1
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 QFE from Technical Support. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

This was the error a Sr. DBA at my client asked me. There error appeared after reading a table from decrypted database which has been encrypted by TDE on SQL Server 2008 R2.

An approximitely 70 GB database with very sensitive data in production is protected by Transparent Database Encryption for both on-disk structure and backup at my client. While building QA environment, the database needs to be restored by DBA, decrypted, and masked in which the process has been done millions of time without any issues. On this occasion, after decryption process is done, accessing one of the tables from the database give such error which is what I have never seen as showing above.

Wow, what heck is that? how to resolve this problem? This is the first thing in my mind. Right after that, I figured that I really know little things which I am not capable to solve the issue that my client can’t deal with it themselves. I sitted side by side with him and replay this error.

– Restore the database and use DBCC CheckDB– No problem.
– Decrypt database — no problem.
– Access the table without any filters while decription — no problem.
– After decryption, access the table — ERROR!

After getting closer look at the error message, it seems like something wrong with the version store. Then I think while I am able to read the table while decryption. I believe there must be some snapshot created while decryption and after decryption, the snapshot is not removed properly by SQL Server. So, the key might be to remove snapshot information in tempdb. but how? Finally, we decided to alter the database, disable all the snapshot settings. After that, everything back to normal.

This is a great lession I learnt from my client’s environment — number of years of experience merely means little. Fundamental knowledge is the most important thing for problem solving. From x number of years of work, you might have seen a lot but there are still trillions of cases waiting for you to explore in next millions of years if you are luck enough to stay conscious that long. So, internals, that’s always what you should learn and dig.